Dynamic sql table/columns

dynsql.sql

-- 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;
$$;