Postgres: Force to have a column in schema

Question

Is it possible to intercept a CREATE table statement and ensure that a given column column1 is present or return an exception?

Does anyone have any example?

My first guess was: Use Event Triggers - but this does not seem to work https://stackoverflow.com/questions/9839746/execute-a-trigger-when-i-create-a-table — madflow, Apr 23 '22 at 15:12
@madflow, what does not work is adding a trigger to a system table as the OP wanted to do in the link you show. — Adrian Klaver, Apr 23 '22 at 15:13
@AdrianKlaver I was referring to "Note that there is no way to directly execute any query on the newly created table, or even get its name. " (Quote from the Link). Maybe am mistaken then, and OP can get the column names. — madflow, Apr 23 '22 at 16:09

klin · Accepted Answer · 2022-04-23T17:45:53.860

CREATE TABLE event trigger fires after a table is created. You can get the OID of the created table and search for its columns in the system catalog pg_attribute. Example:

create or replace function create_table_event() 
returns event_trigger language plpgsql as $$
declare 
    r record;
begin
    for r in 
        select * 
        from pg_event_trigger_ddl_commands() 
        where command_tag = 'CREATE TABLE'
    loop
        if not exists(
            select from pg_attribute
            where attrelid = r.objid
            and attname = 'column1') 
        then
            raise exception 'cannot create table %; table must have "column1" column', r.object_identity;
        end if;
    end loop;
end
$$;

create event trigger create_table_event
    on ddl_command_end when tag in ('CREATE TABLE')
    execute procedure create_table_event();

Postgres: Force to have a column in schema

1 Answers1