Thread: pl/pgsql trigger table attributes

Hiya,
    Not had much experience with tiggers under postgres but am liking them so
far.

    My problem is this.  I am writing an updates system, postgres holds the
master copy of the database, any changes made to this are logged in an
updates table which is monitored by a daemon, which if any updates are
spotted propigates them to the remote servers.

    I have written an initial trigger that spots any changes made to the
tables and creates the correct entries in the updates table, (I even have
the daemon up and running fine too, but thats not the problem :) ).

    The way I have written the pl/pgsql function is specific to the specific
table it is attached to, what I would like is a pl/pgsql function which
could be attached to any table to catch the changes.  To do this I need to
be able to see what attributes the table that has been modified has, if
the function knows them then this isn't a problem, but requires the
function knows them which I don't want.

    Is there a way that a pl/pgsql function can determine the names of the
attributes in a table? or in a record, so that it can pull them out
individually?  Or is this something that is not possible.  If I can get
the names from the NEW record within the trigger that would be excellent.

    The only way I have thought of so far is an additional table which holds
the attribute names for each table being monitored which is used each time
the trigger is fired... I would prefer a less hacky and ikky solution than
this if possible.

Anyone got any suggestions?

Thanks

Graeme Hinchliffe

Graeme Hinchliffe <graeme.hinchliffe@zeninternet.co.uk> writes:
> The way I have written the pl/pgsql function is specific to the specific
> table it is attached to, what I would like is a pl/pgsql function which
> could be attached to any table to catch the changes.

> Is there a way that a pl/pgsql function can determine the names of the
> attributes in a table?

This is somewhere between impractical and impossible in plpgsql; that
language is not designed to handle access to fields with
dynamically-determined names and datatypes.

You could probably do it in pltcl, but if I were you I'd skip the PLs
and go straight to coding it in C, because that's likely where you're
going to end up anyway for performance reasons.  There are some triggers
in the contrib/ tree that do roughly similar things; perhaps you could
use them as prototypes.

Also consider whether you aren't re-inventing the wheel.  It sounds to
me like what you really want is a replication system ... have you looked
at Slony?

            regards, tom lane