basic trigger using OLD not working?

I am going to answer my own post here since I found more info in some
threads posted here.

My real question now is: does the OLD variable work at all in plpgsql?
If not, then some major documentation, books and many posted messages
appear to be wrong!

Here is what the PostgreSQL 7.4.6 Documentation says:
---------------------------------
37.10. Trigger Procedures

PL/pgSQL can be used to define trigger procedures. A trigger procedure
is created with the CREATE FUNCTION command, declaring it as a function
with no arguments and a return type of trigger. Note that the function
must be declared with no arguments even if it expects to receive
arguments specified in CREATE TRIGGER --- trigger arguments are passed
via TG_ARGV, as described below.

When a PL/pgSQL function is called as a trigger, several special
variables are created automatically in the top-level block. They are:

NEW

Data type RECORD; variable holding the new database row for
INSERT/UPDATE operations in row-level triggers. This variable is null in
statement-level triggers.

OLD

Data type RECORD; variable holding the old database row for
UPDATE/DELETE operations in row-level triggers. This variable is null in
statement-level triggers.
...
---------------------------------

This certainly implies that OLD and NEW can be used with plpgsql.
Does anyone know the answer to this?
Are there ways to get the OLD and NEW variables to work in plpgsql?
I am just amazed if so much documentation could be so misleading if this
is not the case.

Regards,
Rick

Rick Casey, Research Associate
Institute for Behavioral Genetics
rick.casey@colorado.edu
303.735.3518

Rick Casey wrote:

Rick Casey <rick.casey@colorado.edu> writes:

Here is the code that creates the delete trigger:
create trigger PEDIGREES_hist_del_trig
AFTER DELETE
on PEDIGREES
EXECUTE PROCEDURE logPedigreesDel();

I think you forgot FOR EACH ROW. By default, the above creates a
STATEMENT trigger, in which you don't have access to individual rows.

regards, tom lane

Thanks much! That met with partial success; but getting closer.

The error message about OLD went away (thankfully!), but there is still
no data from the OLD variable. Here is the code again:

create trigger PEDIGREES_hist_del_trig
AFTER DELETE
on PEDIGREES
FOR EACH ROW
EXECUTE PROCEDURE logPedigreesDel();

CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS '
begin
RAISE EXCEPTION ''OLD.famindid = '', OLD.famindid;
return OLD;
end;
' LANGUAGE plpgsql;

Which when it fires results in:
cgb_lab_data=# \i testphdtrig.sql
psql:testphdtrig.sql:1: ERROR: OLD.famindid =

Regards,
rick

Tom Lane wrote:

rick.casey@colorado.edu (Rick Casey) writes:

CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS '
begin
RAISE EXCEPTION ''OLD.famindid = '', OLD.famindid;

RAISE EXCEPTION ''OLD.famindid = %'', OLD.famindid;
^

return OLD;
end;
' LANGUAGE plpgsql;

--
Remove -42 for email

The RAISE statement requires a % marker for each variable you want to
include in the output text.

--
Guy Rouillier

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rick Casey
Sent: Thursday, February 24, 2005 5:51 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] basic trigger using OLD not working?

Thanks much! That met with partial success; but getting closer.

The error message about OLD went away (thankfully!), but there
is still no data from the OLD variable. Here is the code again:

create trigger PEDIGREES_hist_del_trig
AFTER DELETE
on PEDIGREES
FOR EACH ROW
EXECUTE PROCEDURE logPedigreesDel();

CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS
'
begin
RAISE EXCEPTION ''OLD.famindid = '', OLD.famindid;
return OLD;
end;
' LANGUAGE plpgsql;

Which when it fires results in:
cgb_lab_data=# \i testphdtrig.sql
psql:testphdtrig.sql:1: ERROR: OLD.famindid =

Regards,
rick

Tom Lane wrote:

Rick Casey <rick.casey@colorado.edu>
<mailto:rick.casey@colorado.edu> writes:

Here is the code that creates the delete
trigger:
create trigger PEDIGREES_hist_del_trig
AFTER DELETE
on PEDIGREES
EXECUTE PROCEDURE logPedigreesDel();

I think you forgot FOR EACH ROW. By default, the above
creates a
STATEMENT trigger, in which you don't have access to
individual rows.

regards, tom lane

Rick Casey wrote:

I am going to answer my own post here since I found more info in some
threads posted here.

My real question now is: does the OLD variable work at all in plpgsql?
If not, then some major documentation, books and many posted messages
appear to be wrong!

It certainly works in BEFORE DELETE triggers. I use several of them
(PostgreSQL 7.4).

--
Alban Hertroys
MAG Productions

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

I think you have created a statement level trigger (If they existed in
7.4.7...) by not including FOR EACH ROW in your create statement. In
statement level triggers, there is no OLD or NEW.

Rick Casey <rick.casey@colorado.edu> 02/24/05 1:22 PM >>>

Hello all,

I am trying to a simple thing: create a log history of deletes, and
updates; but which I am having trouble getting to work in PG 7.4.7
(under Debian Linux 2.6.8).

I have reduced my code to the following trivial case:

Here is the code that creates the delete trigger:
create trigger PEDIGREES_hist_del_trig
AFTER DELETE
on PEDIGREES
EXECUTE PROCEDURE logPedigreesDel();

Here is the trigger code: (famindid is an integer field in the Pedigrees

table):

CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS '
DECLARE
test integer;
begin
test := OLD.famindid;
RAISE EXCEPTION ''OLD.famindid = '', test;
return OLD;
end;
' LANGUAGE plpgsql;

Here is the error message returned:
psql:testphdtrig.sql:1: ERROR: record "old" is not assigned yet
DETAIL: The tuple structure of a not-yet-assigned record is
indeterminate.
CONTEXT: PL/pgSQL function "logpedigreesdel" line 4 at assignment

Would *really appreciate* any suggestions! This could help us decide
whether to PostGres for a major project...

thanks --rick

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Yes, thank you, I corrected my function from statement level to row level.
This did get rid of the error message. However, I still get no output from
an OLD variable that should contain data: see the test variable in the
simple case below.

How else can I test OLD variables? This is the simplest test case I can
think of. Any suggestions would be appreciated!

Thanks,
Rick

Try creating the trigger on BEFORE DELETE.

--elein

On Fri, 2005-02-25 at 17:14 -0700, Rick.Casey@colorado.edu wrote:

Yes, thank you, I corrected my function from statement level to row level.
This did get rid of the error message. However, I still get no output from
an OLD variable that should contain data: see the test variable in the
simple case below.
...

RAISE EXCEPTION ''OLD.famindid = '', test;

as has already been pointed out, you need to put a % marker
in your format string.

RAISE EXCEPTION ''OLD.famindid = %'', test;

gnari

Rick.Casey@colorado.edu presumably uttered the following on 02/25/05 19:14:

Yes, thank you, I corrected my function from statement level to row level.
This did get rid of the error message. However, I still get no output from
an OLD variable that should contain data: see the test variable in the
simple case below.

How else can I test OLD variables? This is the simplest test case I can
think of. Any suggestions would be appreciated!

Thanks,
Rick

I think you have created a statement level trigger (If they existed in
7.4.7...) by not including FOR EACH ROW in your create statement. In
statement level triggers, there is no OLD or NEW.

Rick Casey <rick.casey@colorado.edu> 02/24/05 1:22 PM >>>

Hello all,

I am trying to a simple thing: create a log history of deletes, and
updates; but which I am having trouble getting to work in PG 7.4.7
(under Debian Linux 2.6.8).

I have reduced my code to the following trivial case:

Here is the code that creates the delete trigger:
create trigger PEDIGREES_hist_del_trig
AFTER DELETE
on PEDIGREES
EXECUTE PROCEDURE logPedigreesDel();

Here is the trigger code: (famindid is an integer field in the Pedigrees

table):

CREATE OR REPLACE FUNCTION logPedigreesDel() RETURNS TRIGGER AS '
DECLARE
test integer;
begin
test := OLD.famindid;
RAISE EXCEPTION ''OLD.famindid = '', test;
return OLD;
end;
' LANGUAGE plpgsql;

Need a place holder for your variable in your RAISE expression (like a
printf syntax):

RAISE EXCEPTION ''OLD.famindid = %'', test;

btw, if you just want to see the variable without having your function
bail on you, try RAISE NOTICE ''OLD.famindid = %'', test;

Sven

Hey, thanks to everyone who replied to my questions: problem solved!

I needed to: 1) do BEFORE DELETE to see the OLD variables, and 2) use a
placeholder in my format string in the RAISE EXCEPTION/NOTICE statement.

Open source newsgroups rock!

--rick

Rick Casey, Research Associate
Institute for Behavioral Genetics
rick.casey@colorado.edu
303.735.3518

Sven Willenberger wrote: