PostgreSQL Discussion - Trigger and arguments question

Author

Trigger and arguments question

Hervé Inisan

2005-05-26, 8:23 pm

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?
Thanks,
-- Hervé Inisan.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Jaime Casanova

2005-05-26, 8:24 pm

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 ;)

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Alban Hertroys

2005-05-27, 7:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Hervé Inisan

2005-05-27, 7:23 am

> Hervé Inisan wrote:
> this value in
> field in NEW or OLD.
>
> 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.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Stephan Szabo

2005-05-27, 9:23 am

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.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links