Skip site navigation (1) Skip section navigation (2)

How to determine field names in a trigger?

From: Justin Clift <jc(at)telstra(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: How to determine field names in a trigger?
Date: 2004-06-29 03:59:11
Message-ID: 40E0E90F.4020003@telstra.net (view raw or flat)
Thread:
Lists: pgsql-general
Hi all,

I'm creating a centralised table to keep a log of changes in other tables.

In thinking about the PL/pgSQL trigger to write and attach to the 
monitored tables (probably a row level AFTER trigger), I can see two 
approaches:

  a) Write a separate PL/pgSQL function for each table, with the hard 
coded field names in the function.

or

  b) Write one PL/pgSQL function that can be used in the triggers for 
all of the monitored tables.

It sounds like b) would be most time effective to write and maintain, 
but in looking through the PG docs I haven't seen anything that says how 
to determine the field names in the OLD nor NEW records, nor how many 
fields there are:

http://www.postgresql.org/docs/7.4/static/plpgsql-trigger.html

For example, let's say I have two tables.  One is called table_a, and 
has two columns, and the other is table_b and has three columns.  The 
advantage of having the PL/pgSQL trigger knowing the number of fields 
and their names in the OLD or NEW rows would be in being able to do this:

(pseudo code)

LOOP
     -- If we've processed all the fields, then exit
     IF i > OLD.number_of_fields
         EXIT;
     END IF;

     -- Check if the next field was changed, and if so, record it
     IF OLD.nextfield <> NEW.nextfield
         INSERT INTO log_table (table, field, old_val, new_val)
             VALUES (TG_RELNAME, nextfield, OLD.nextfield,
                     NEW.nextfield);
     END IF;

     -- Increment the loop counter
     i := i + 1;
END LOOP

Are there any way to do this kind of thing for triggers with PL/pgSQL at 
present?

Regards and best wishes,

Justin Clift


Responses

pgsql-general by date

Next:From: Együd CsabaDate: 2004-06-29 04:52:56
Subject: Re: Performance problem on RH7.1
Previous:From: Carl E. McMillinDate: 2004-06-29 00:04:45
Subject: Re: Seeing uncommitted transactions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group