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?

CodePudding user response：

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();

Read more in the documentation:

• Overview of Event Trigger Behavior
• Event Trigger Functions
• CREATE EVENT TRIGGER