Обсуждение: Trigger and arguments question

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.

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.