Re: Triggers with arguments - Mailing list pgsql-general

From Tom Lane
Subject Re: Triggers with arguments
Date
Msg-id 25967.963447470@sss.pgh.pa.us
Whole thread Raw
In response to Triggers with arguments  (Scott Holmes <sholmes@pacificnet.net>)
List pgsql-general
Scott Holmes  <sholmes@pacificnet.net> writes:
> CREATE FUNCTION del_stxnoted () RETURNS opaque AS '
>   DECLARE
>     fname alias for $1;
>     rkey alias for $2;
>   BEGIN
>     delete from stxnoted where filename = fname
>        and record_key = rkey;
>   END;'
> LANGUAGE 'plpgsql';

> create trigger del_location_trig
> after delete
>   on location
>   for each row
>     execute procedure del_stxnoted("location", 'old.wher');

> Postgres will not create this trigger as it does not recognize the function
> del_stxnoted as actually existing.

Uh, the trigger creates just fine for me.  I think your problem is
with the way you're trying to get at the trigger arguments in the
function body.  There's an example of the right way in the plpgsql
regress test:

create function tg_chkslotname() returns opaque as '
begin
    if substr(new.slotname, 1, 2) != tg_argv[0] then
        raise exception ''slotname must begin with %'', tg_argv[0];
    end if;
    return new;
end;
' language 'plpgsql';

create trigger tg_chkslotname before insert
    on PSlot for each row execute procedure tg_chkslotname('PS');

create trigger tg_chkslotname before insert
    on WSlot for each row execute procedure tg_chkslotname('WS');


The documentation mentions tg_argv[] but doesn't seem to give an example
:-(

            regards, tom lane

pgsql-general by date:

Previous
From: Erich
Date:
Subject: Re: Figured it out (psql and Gnu readline)
Next
From: Tom Lane
Date:
Subject: Re: Figured it out (psql and Gnu readline)