Skip to content

Instantly share code, notes, and snippets.

@sanderhahn
Created June 4, 2020 13:05
Show Gist options
  • Save sanderhahn/1b874732c0dd27666042cc1d91feaedb to your computer and use it in GitHub Desktop.
Save sanderhahn/1b874732c0dd27666042cc1d91feaedb to your computer and use it in GitHub Desktop.
Dynamic sql table/columns
-- psql $HASURA_GRAPHQL_DATABASE_URL -f dynsql.sql
\set ON_ERROR_STOP on
drop schema if exists meta cascade;
create schema meta;
set search_path to 'meta', 'public';
drop table if exists db_column;
drop table if exists db_table;
drop table if exists db_datatype;
create table db_table (
tablename varchar not null unique
);
create table db_datatype (
datatypename varchar not null unique
);
insert into db_datatype (datatypename) values ('integer'), ('text');
create table db_column (
tablename varchar not null references db_table(tablename) on update cascade on delete cascade,
columnname varchar not null,
datatypename varchar not null references db_datatype(datatypename),
unique(tablename, columnname)
);
create or replace function db_table_mutation()
returns trigger
language plpgsql
as $$
declare
sql text;
begin
-- https://www.postgresql.org/docs/current/plpgsql-trigger.html
raise notice 'TG_OP % OLD % NEW %', TG_OP, OLD, NEW;
case
when TG_OP = 'INSERT' then
sql := 'create table meta.' || quote_ident(new.tablename) || '()';
when TG_OP = 'DELETE' then
sql := 'drop table meta.' || quote_ident(old.tablename);
when TG_OP = 'UPDATE' then
if new.tablename != old.tablename then
sql := 'alter table meta.' || quote_ident(old.tablename) || ' rename to ' || quote_ident(new.tablename);
end if;
end case;
if sql is not null then
-- https://www.postgresql.org/docs/current/sql-execute.html
execute sql;
end if;
return new;
end;
$$;
create trigger db_table_trigger
before insert or update or delete on db_table
for each row execute procedure db_table_mutation();
create or replace function db_column_mutation()
returns trigger
language plpgsql
as $$
declare
alter_table text;
sql text;
begin
raise notice 'TG_OP % OLD % NEW %', TG_OP, OLD, NEW;
alter_table := 'alter table meta.' || quote_ident(coalesce(old.tablename, new.tablename));
case
when TG_OP = 'INSERT' then
execute alter_table || ' add column ' || quote_ident(new.columnname) || ' ' || new.datatypename;
when TG_OP = 'DELETE' then
execute alter_table || ' drop column ' || quote_ident(old.columnname);
when TG_OP = 'UPDATE' then
if new.columnname != old.columnname then
execute alter_table || ' rename ' || quote_ident(old.columnname) || ' to ' || quote_ident(new.columnname);
end if;
if new.datatypename != old.datatypename then
sql := alter_table || ' alter column ' || quote_ident(new.columnname) || ' type ' || new.datatypename
|| ' using ' || quote_ident(new.columnname) || '::' || new.datatypename;
raise notice 'SQL %', sql;
execute sql;
end if;
end case;
return new;
end;
$$;
create trigger db_column_trigger
before insert or update or delete on db_column
for each row execute procedure db_column_mutation();
do $$
begin
insert into db_table (tablename) values ('contact');
update db_table set tablename = 'contact' where tablename = 'contact';
insert into db_column (tablename, columnname, datatypename) values ('contact', 'name', 'text');
update db_column set (tablename, columnname, datatypename) = ('contact', 'my_name', 'integer')
where (tablename, columnname) = ('contact', 'name');
delete from db_column where (tablename, columnname) = ('contact', 'my_name');
update db_table set tablename = 'my_contact' where tablename = 'contact';
delete from db_table where tablename = 'my_contact';
rollback;
end;
$$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment