skip to Main Content

I would like to enforce a rule such that when people are creating table without primary key, it throws an error. Is it possible to be done from within pgdb?

2

Answers


  1. EDIT: Someone else has answered regarding how to test the existence of primary keys, which completes Part 2 below. You will have to combine both answers for the full solution.

    The logic fits inside several event triggers (also see documentation for the create command).

    First point to note is the DDL commands this can apply to, all documented here.

    Part 1: CREATE TABLE AS & SELECT INTO

    If I am not wrong, CREATE TABLE AS and SELECT INTO never add constraints on the created table, they must be blocked with an event trigger that always raises an exception.

    CREATE OR REPLACE FUNCTION block_ddl()
     RETURNS event_trigger
     LANGUAGE plpgsql AS
    $$
    BEGIN
        RAISE EXCEPTION 'It is forbidden to create tables using command: %', tg_tag ;
    END;
    $$;
    
    CREATE EVENT TRIGGER AdHocTables_forbidden
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE AS', 'SELECT INTO')
    EXECUTE FUNCTION block_ddl();
    

    Note your could define the trigger to be ON ddl_command_start`. It makes it a little bit faster but does not go well with the full code I posted at the end.
    See the next, less straightforward part for the rest of the explanations.

    Part 2: Regular CREATE TABLE & ALTER TABLE

    This case is more complex, as we want to block only some commands but not all.

    The function and event trigger below do:

    1. Output the whole command being passed.
    2. Break the command into its subparts.
      To do it, it uses the pg_event_trigger_ddl_commands() (documentation here), which BTW is the reason why this trigger had to be on ddl_command_end.
      You will note that when adding a primary key, a CREATE INDEX is caught too.
    3. In the case of the function below, raises an exception to block the creation in all cases (so you can test it without dropping the table you create every time).

    Here is the code:

    CREATE OR REPLACE FUNCTION pk_enforced()
     RETURNS event_trigger
     LANGUAGE plpgsql AS
    $$
    DECLARE r RECORD;
    BEGIN
        RAISE NOTICE 'Caught command %', (SELECT current_query());
        FOR r IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
            RAISE NOTICE 'Caught inside command % (%)', r.command_tag, r.object_identity;
        END LOOP;
        RAISE EXCEPTION 'Blocking the Creation';
    END;
    $$;
    
    CREATE EVENT TRIGGER pk_is_mandatory
    ON ddl_command_end
    WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE')
    EXECUTE FUNCTION pk_enforced();
    

    Additional notes:

    You can prevent these constraints from being enforced on a temporary table by tested the schema_name is not pg_temp.
    The full code, including this test and with credit to jian for the function he posted:

    CREATE OR REPLACE FUNCTION public.pk_enforced()
        RETURNS event_trigger
        LANGUAGE 'plpgsql'
    AS $BODY$
    DECLARE
    obj RECORD;
    table_name text;
    BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands ()
    LOOP
        IF obj.schema_name = 'pg_temp' THEN
            return;
        END IF;
        IF obj.object_type ~ 'table' THEN
            table_name := obj.object_identity;
        END IF;
    END LOOP;
    IF NOT EXISTS (
        SELECT 
        FROM pg_index i
        JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
        WHERE i.indrelid = table_name::regclass
          AND (i.indisprimary OR i.indisunique)) THEN
    RAISE EXCEPTION 'A primary key or a unique constraint is mandatory to perform % on %.', tg_tag, obj.object_identity;
    END IF;
    END;
    $BODY$;
    
    CREATE OR REPLACE FUNCTION public.block_ddl()
        RETURNS event_trigger
        LANGUAGE 'plpgsql'
    AS $BODY$
    DECLARE
    obj RECORD;
    BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands ()
    LOOP
        IF obj.schema_name = 'pg_temp' THEN
            return;
        END IF;
    END LOOP;
        RAISE EXCEPTION 'DDL command ''%'' is blocked.', tg_tag ;
    END;
    $BODY$;
    
    CREATE EVENT TRIGGER pk_is_mandatory ON DDL_COMMAND_END
        WHEN TAG IN ('CREATE TABLE', 'ALTER TABLE')
        EXECUTE PROCEDURE public.pk_enforced();
        
    CREATE EVENT TRIGGER adhoctables_forbidden ON DDL_COMMAND_END
        WHEN TAG IN ('CREATE TABLE AS', 'SELECT INTO')
        EXECUTE PROCEDURE public.block_ddl();
    
    Login or Signup to reply.
  2. DROP EVENT TRIGGER trig_test_event_trigger_table_have_primary_key;
    
    CREATE OR REPLACE FUNCTION test_event_trigger_table_have_primary_key ()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$
    DECLARE
        obj record;
        object_types text[];
        table_name text;
    BEGIN
        FOR obj IN
        SELECT
            *
        FROM
            pg_event_trigger_ddl_commands ()
            LOOP
                RAISE NOTICE 'classid: % objid: %,object_type: %
        object_identity: % schema_name: % command_tag: %' , obj.classid , obj.objid , obj.object_type , obj.object_identity , obj.schema_name , obj.command_tag;
                IF obj.object_type ~ 'table' THEN
                    table_name := obj.object_identity;
                END IF;
                object_types := object_types || obj.object_type;
            END LOOP;
        RAISE NOTICE 'table name: %' , table_name;
        IF EXISTS (
            SELECT
            FROM
                pg_index i
                JOIN pg_attribute a ON a.attrelid = i.indrelid
                    AND a.attnum = ANY (i.indkey)
            WHERE
                i.indisprimary
                AND i.indrelid = table_name::regclass) IS FALSE THEN
        RAISE EXCEPTION ' no primary key, this table not created';
    END IF;
    END;
    $$;
    
    
    CREATE EVENT TRIGGER trig_test_event_trigger_table_have_primary_key ON ddl_command_end
        WHEN TAG IN ('CREATE TABLE')
            EXECUTE FUNCTION test_event_trigger_table_have_primary_key ();
    

    demo:

    DROP TABLE a3;
    DROP TABLE a4;
    DROP TABLE a5;
    
    CREATE TABLE a3 (
        a int
    );
    
    CREATE TABLE a4 (
        a int PRIMARY KEY
    );
    
    CREATE TABLE a5 (
        a1 int UNIQUE
    );
    

    Only table a4 will be created.

    related post: PL/pgSQL checking if a row exists
    https://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search