Is it possible to dynamically loop through a table's columns?

Question

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';
-- more code

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?

Poleyn

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.

Pickle

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.*)

Hootman

Yes. If all you're doing is testing whether any column values have changed and you don't need to know the specific column(s) that have changed, Stephen's 2nd snippet is definitely the way to go. Concise and never needs maintenance! – Holliehollifield

Archangel might find it useful to combine that test with knowledge of whether Locked > 0 and whether locked has changed. Additionally IS DISTINCT FROM deals with nulls in a less surprising way than <> – Hootman

Answer 3

In Postgres 9.0 or later add a WHEN clause to your trigger definition (CREATE TRIGGER statement):

CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD IS DISTINCT FROM NEW)  -- parentheses required!
EXECUTE PROCEDURE ...;

Only possible for triggers BEFORE / AFTER UPDATE, where both OLD and NEW are defined. You'd get an exception trying to use this WHEN clause with INSERT or DELETE triggers.

And radically simplify the trigger function accordingly:

...
IF OLD.locked > 0 THEN
   RAISE EXCEPTION 'Message';
END IF;
...

No need to test IF TG_OP='UPDATE' ... since this trigger only works for UPDATE anyway.

Or move that condition in the WHEN clause, too:

CREATE TRIGGER foo
BEFORE UPDATE
FOR EACH ROW
WHEN (OLD.locked > 0
  AND OLD IS DISTINCT FROM NEW)
EXECUTE PROCEDURE ...;

Leaving only an unconditional RAISE EXCEPTION in your trigger function, which is only called when needed to begin with.

Read the fine print:

In a BEFORE trigger, the WHEN condition is evaluated just before the function is or would be executed, so using WHEN is not materially different from testing the same condition at the beginning of the trigger function. Note in particular that the NEW row seen by the condition is the current value, as possibly modified by earlier triggers. Also, a BEFORE trigger's WHEN condition is not allowed to examine the system columns of the NEW row (such as oid), because those won't have been set yet.

In an AFTER trigger, the WHEN condition is evaluated just after the row update occurs, and it determines whether an event is queued to fire the trigger at the end of statement. So when an AFTER trigger's WHEN condition does not return true, it is not necessary to queue an event nor to re-fetch the row at end of statement. This can result in significant speedups in statements that modify many rows, if the trigger only needs to be fired for a few of the rows.

Related:

• Fire trigger on update of columnA or ColumnB or ColumnC

To also address the question title

Is it possible to dynamically loop through a table's columns?

Yes. Examples:

• Handle result when dynamic SQL is in a loop
• Removing all columns with given name

Typescript

Answer 4

Iteration over RECORD variable inside trigger

Ula

Answer 5

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.

Coruscation