Trigger and arguments question

On 5/26/05, Hervé Inisan <typo3@self-access.com> wrote:

Hi everybody!

I have a trigger like this:

CREATE TRIGGER mytrigger
AFTER INSERT OR UPDATE OR DELETE
ON myschema.mytable
FOR EACH ROW
EXECUTE PROCEDURE myschema.myfunction(myarg);

It sends an argument to myfunction(), and I can retrieve this value in
TG_ARGV[0]. Fine.
What I'm trying to do is using TG_ARGV[0] to point to a field in NEW or OLD.
Is it possible?

Something like NEW.TG_ARGV[0]...

I'm trying to write a kind of generic function which I could use on multiple
tables with different field names (myarg being the field name).
But I can't get it to work.

Any clues or other solutions?

No. the argument of the trigger must be a string literal defined at
creation time.

maybe you better solution is simply a function

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

Hervé Inisan wrote:

Hi everybody!

I have a trigger like this:

CREATE TRIGGER mytrigger
AFTER INSERT OR UPDATE OR DELETE
ON myschema.mytable
FOR EACH ROW
EXECUTE PROCEDURE myschema.myfunction(myarg);

It sends an argument to myfunction(), and I can retrieve this value in
TG_ARGV[0]. Fine.
What I'm trying to do is using TG_ARGV[0] to point to a field in NEW or OLD.
Is it possible?

You'll be missing OLD and NEW on INSERT and DELETE respectively, I'm
afraid. You may want to split your triggers for different events.

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

Hervé Inisan wrote:

Hi everybody!

I have a trigger like this:

CREATE TRIGGER mytrigger
AFTER INSERT OR UPDATE OR DELETE
ON myschema.mytable
FOR EACH ROW
EXECUTE PROCEDURE myschema.myfunction(myarg);

It sends an argument to myfunction(), and I can retrieve

this value in

TG_ARGV[0]. Fine.
What I'm trying to do is using TG_ARGV[0] to point to a

field in NEW or OLD.

Is it possible?

You'll be missing OLD and NEW on INSERT and DELETE
respectively, I'm afraid. You may want to split your triggers
for different events.

Thank you all for your answers.
I tried with EXECUTE, with you're right: no way to build a NEW.field
dynamically.

-- Hervé Inisan.

On Thu, 26 May 2005, [iso-8859-1] Herv� Inisan wrote:

It sends an argument to myfunction(), and I can retrieve this value in
TG_ARGV[0]. Fine.
What I'm trying to do is using TG_ARGV[0] to point to a field in NEW or OLD.
Is it possible?

Something like NEW.TG_ARGV[0]...

I'm trying to write a kind of generic function which I could use on multiple
tables with different field names (myarg being the field name).
But I can't get it to work.

Any clues or other solutions?

If you're using plpgsql, that's not possible. It should be possible in
some of the other pl languges, however.