469,579 Members | 1,830 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

How to determine field names in a trigger?

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/s...l-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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #1
3 4088
On Tue, Jun 29, 2004 at 01:59:11PM +1000, Justin Clift wrote:

Justin,
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:


I think you can do this very easily with PL/Tcl. For a somewhat
unrelated example, see General Bits issue #47,

http://www.varlena.com/GeneralBits/47.php

_I think_ there are examples closer to what you want to achieve in the
archives. The array of column names in a trigger is $TG_relatts.

Hope this helps,

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La libertad es como el dinero; el que no la sabe emplear la pierde" (Alvarez)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2
Alvaro Herrera wrote:
<snip>
I think you can do this very easily with PL/Tcl. For a somewhat
unrelated example, see General Bits issue #47,

http://www.varlena.com/GeneralBits/47.php

_I think_ there are examples closer to what you want to achieve in the
archives. The array of column names in a trigger is $TG_relatts.
Thanks Alvaro.

Didn't find any good examples in the archives (looked there before
asking here), even though there are a few people over time asking the
same question.

In PL/pgSQL at present (PG 7.4.3), I'm thinking the only approach may be
to get the name of the table (TG_RELNAME) and then go and look up it's
structure in pg_attribute or something (the same as shown by
information_schema.columns). Not sure how to do that either yet, and a
better solution would be nifty.

PL/TCL does seem to provide that extra bit of info to functions/triggers
that I need, whereas PL/pgSQL doesn't. I'd prefer not to have to load
further PL's into the database, but it's worth looking at anyway.

:)

Regards and best wisehs,

Justin Clift
Hope this helps,


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #3
Justin Clift <jc@telstra.net> writes:
PL/TCL does seem to provide that extra bit of info to functions/triggers
that I need, whereas PL/pgSQL doesn't.
It's not so much that there's more info available as that pltcl is
better suited for working with dynamically generated queries. plpgsql
is handicapped by the fact that it wants to precompute and cache query
plans. That's a performance win when you're working with known tables
but it really gets in the way for dynamically addressing columns.
I'd prefer not to have to load
further PL's into the database, but it's worth looking at anyway.


Different needs, different tools.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Andre | last post: by
4 posts views Thread by Josué Maldonado | last post: by
5 posts views Thread by lennart | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.