Postgresql – How to validate column while creating Table

postgresqlpostgresql-9.1postgresql-9.2

Need to validate the column while creating the tables in postgres , An example before developer create table We are in need to validate the Audit columns,

Example :

create table tab_1 (
a integer,
b integer,
Audit1 integer,
audit2 integer)

Should not create the tables without the audit columns , Is it possible by trigger or any other concept.

Best Answer

PostgreSQL does not support triggers on system tables, so there's no way you can write a normal trigger that fires when table definitions are changed.

PostgreSQL 9.4 introduces a new feature called event triggers. (More options have been added for event triggers in the coming 9.5 release too). You could use an event trigger to validate that table definitions follow rules you set out by writing suitable validation code in PL/PgSQL. You can query information_schema and/or pg_catalog to find details of the tables, and you can abort the transaction with an ERROR if you don't like what you see.

That said, this seems like a complicated technical solution to a simple people problem. I wouldn't advise going down this route when your code review process can be fixed to catch these sorts of problems, your app can be modified to query the schema and emit warnings on tables that aren't defined how you require, etc.

Related Solutions

Postgresql – Inherit audit columns and triggers

From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

The DELETE and UPDATE triggers need to be on the child tables. An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;

PostgreSQL – Best Way to Drop/Recreate Table with Dependent Views

If you have the luxory of exclusive access a very simple method is to draw a backup with pg_dump, reorder columns as you chose in the CREATE TABLE scripts of the dump and restore the database. Other parts of the dump do not depend on column order. For instance, COPY statements list columns explicitly.

If you don't have this luxory, the whole operation is a pain. You need an exclusive lock on the table while dropping and recreating it - and all depending objects like views in its tail.
And concurrent queries on the table will still produce exceptions because they wait for the lock to go away, but then the table is gone (the table name has been resolved to a particular OID early in the process).

The Postgres Wiki on "Alter column position" has some more advice.

Decoupling logical column ordering from physical column ordering has been discussed repeatedly on pgsql-hackers, there is an entry in the ToDo Wiki by the name:

Allow column display reordering by recording a display, storage, and permanent id for every column?

And efforts are being made to maybe get this into Postgres 9.6:

Best Answer

Related Solutions

Postgresql – Inherit audit columns and triggers

PostgreSQL – Best Way to Drop/Recreate Table with Dependent Views

Related Question