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?

CodePudding user response：

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

CodePudding user response：

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_start
WHEN TAG IN ('CREATE TABLE AS', 'SELECT INTO')
EXECUTE FUNCTION block_ddl();

See the next, less straightforward part for explanations.

Part 2: Regular CREATE TABLE

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

Unlike the previous trigger, we want it to be a ddl_command_end event trigger, I will explain why later.

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')
EXECUTE FUNCTION pk_enforced();

PART 3: ALTER TABLE

A similar event trigger as in part 2 must be created to prevent users from dropping a primary key.