Re: basic trigger using OLD not working?

Rick Casey <rick.casey@xxxxxxxxxxxx> · Thu, 24 Feb 2005 15:13:52 -0700

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@xxxxxxxxxxxx
303.735.3518

Rick Casey wrote:

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

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