Postgresql: is it possible to dynamically move around a table column?

Question

I have a trigger function to test a table that has the following code snippet -

IF TG_OP='UPDATE' THEN IF OLD.locked > 0 AND ( OLD.org_id <> NEW.org_id OR OLD.document_code <> NEW.document_code OR -- Other columns .......................... ) THEN RAISE EXCEPTION 'Message'; /* Rest of the codes */ ................................. 

So, I statically check the entire column value with its previous value to ensure integrity. Now, every time my business logic changes, and I have to add new columns to this table, I will have to change this trigger every time. I thought it would be better if I could dynamically check all the columns of this table without explicitly specifying their name.

How can I do that?

Answer 1

Take a look at information_schema, "columns" will appear. Run the query to get all the current column names from the table that fired the trigger:

 SELECT column_name FROM information_schema.columns WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME; 

Scroll through the result and there you go!

More information can be found in the excellent guide .

Answer 2

From the 9.0 beta2 documentation on WHEN in triggers that can be used in earlier versions inside the trigger body:

OLD.* IS DISTINCT FROM NEW.*

or possibly ( from notes 8.2)

IF row(new.*) IS DISTINCT FROM row(old.*)

Answer 3

Iterate over a RECORD variable inside a trigger

Answer 4

Use pl / perl or pl / python. They are much better suited for such tasks. much .

You can also install hstore-new and use its row-> hstore semantics, but this is definitely not a good idea when using regular data types.