Skip to content

Instantly share code, notes, and snippets.

@selenamarie
Last active December 17, 2015 17:29
Show Gist options
  • Star 15 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save selenamarie/5646494 to your computer and use it in GitHub Desktop.
Save selenamarie/5646494 to your computer and use it in GitHub Desktop.
I wrote this as prep for a talk about JSON datatype and PLV8: https://speakerdeck.com/selenamarie/schema-liberation-with-json-and-plv8-and-postgres
CREATE SCHEMA liberated;
CREATE OR REPLACE FUNCTION public.liberate()
RETURNS boolean
LANGUAGE plv8
AS $function$
var tables = plv8.execute(
"select relname FROM pg_catalog.pg_class"
+ " c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace"
+ " where relkind = 'r' and n.nspname = 'public'");
var tablesLength = tables.length;
for (var i = 0; i < tablesLength; i++) {
plv8.elog(NOTICE, tables[i]['relname']);
var code = "create table liberated."
+ tables[i]['relname']
+ " as ( select row_to_json("
+ tables[i]['relname']
+ ") as liberated_json "
+ "from select * from "
+ tables[i]['relname']
+ ")" ;
plv8.execute(code);
}
return 1;
$function$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment