Re: triggers, transactions and locks

On Mon, 6 Dec 2004, C G wrote:

> Is there a way to use locks within a trigger? My example below gives the
> error:

It's not the lock that's the problem I think. The begin work is failing
because you can't start a transaction inside the function. Were you trying
to release the lock at the commit in the function?

> CREATE FUNCTION insert_into_t1() RETURNS trigger AS'
>         DECLARE
>                 set_qry text;
>         BEGIN
>
>                 set_qry:=''BEGIN WORK'';
>                 execute set_qry;
>                 LOCK t1;
>                 INSERT INTO t2 VALUES (11);
>                 COMMIT;
>                 RETURN NEW;
>         END;
> ' LANGUAGE 'plpgsql' SECURITY DEFINER;