postgresql: Is it possible to dynamically loop through a table's column

Question

Hi, I have a trigger function for a table test which 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 am statically checking all the column's new value with its previous value to ensure integrity. Now every time my business logic changes and I have to add new columns into that table, I will have to modify this trigger each time. I thought it would be better if somehow I could dynamically check all the columns of that table, without explicitly typing their name.

How can it be done?

Answer 1

Take a look at the information_schema, there is a view "columns". Execute a query to get all current columnnames 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;

Loop through the result and there you go!

More information can be found in the fine manual.

Answer 2

From 9.0 beta2 documentation about WHEN clause in triggers, which might be able to be used in earlier versions within the trigger body:

OLD.* IS DISTINCT FROM NEW.*

or possibly (from 8.2 release notes)

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

Answer 3

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

You can also install hstore-new, and use it's row->hstore semantics, but that's definitely not a good idea when using normal datatypes.