Re: basic trigger using OLD not working? - Mailing list pgsql-general

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:

> 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