postgres 9.3+: constraint to ensure table and column exist in database

Question

How can I ensure a new record contains values that refer to a schema, table, and column that currently exist in the database?

For example, given a table:

CREATE TEMP TABLE "column_reference" (
      "gid"    SERIAL PRIMARY KEY
    , "val"    INTEGER
    , "schema" TEXT
    , "table"  TEXT
    , "column" TEXT
);

how can I ensure schema.table.column exists?

I tried a fkey to information_schema.columns, but, of course, foreign keys to views are disallowed.

It also appears from the columns view definition that I need several tables in order to get the schema, table, and column names so I can't create a single foreign key to the source tables.

My current workaround is to manually create a __columns table from the information_schema.columns view and reference it instead. This works given the control I happen to have on this project at this point in time, but am looking for a permanent, dynamic solution.

Is there a different constraint or method I could use?

joanolo · Accepted Answer · 2017-02-11 00:18:43Z

You can create a trigger function that checks what you want, and associate this function with a trigger which is fired BEFORE an INSERT or an UPDATE of the table:

This could be your trigger function:

CREATE FUNCTION column_reference_check()
    RETURNS trigger
    LANGUAGE 'plpgsql'
AS 
$BODY$
begin
    /* Check for the existence of the required column */
    if EXISTS (
          SELECT * 
          FROM information_schema.columns 
          WHERE
                  table_schema = new.schema
              AND table_name   = new.table
              AND column_name  = new.column )
    then
        /* Everything Ok */
        return new ;
    else
        /* This is approx. what would happen if you had a constraint */
        RAISE EXCEPTION 'Trying to insert non-matching (%, %, %)', new.schema, new.table, new.column ;

        /* As an alternative, you could also just return NULL
           As a result, the row is *not* inserted, but execution continues */
        return NULL ;
    end if ;
 end ;
$BODY$;

To associate this function with a trigger, you'd use:

CREATE TRIGGER column_reference_check_trg
    BEFORE INSERT OR UPDATE OF "schema", "table", "column"
    ON column_reference
    FOR EACH ROW
    EXECUTE PROCEDURE column_reference_check();

Now you can try to perform the following INSERT, that should succeed:

INSERT INTO column_reference 
   VALUES (2, 1, 'pg_catalog', 'pg_statistic', 'starelid');

But if you try this one:

INSERT INTO column_reference 
   VALUES (-1, 1, 'false_schema', 'false_table', 'false_column');

... you get an exception:

ERROR:  Trying to insert non-matching (false_schema, false_table, false_column)
CONTEXT:  PL/pgSQL function column_reference_check() line 16 at RAISE

Collectives™ on Stack Overflow

postgres 9.3+: constraint to ensure table and column exist in database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related