Postgres Pro
Downloads
Home   >   mailing lists

Thread: basic trigger using OLD not working?

basic trigger using OLD not working?

From
Rick Casey
Date:
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

Re: basic trigger using OLD not working?

From
Rick Casey
Date:
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

Re: basic trigger using OLD not working?

From
Tom Lane
Date:
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

Re: basic trigger using OLD not working?

From
Rick Casey
Date:
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> 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

Re: basic trigger using OLD not working?

From
"Guy Rouillier"
Date:
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> 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

Re: basic trigger using OLD not working?

From
Alban Hertroys
Date:
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

Re: basic trigger using OLD not working?

From
"Ian Harding"
Date:
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

Re: basic trigger using OLD not working?

From
Rick.Casey@colorado.edu
Date:
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;
>
>
> 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 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: basic trigger using OLD not working?

From
elein@varlena.com (elein)
Date:
Try creating the trigger on BEFORE DELETE.

--elein

On Fri, Feb 25, 2005 at 05:14:18PM -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.
>
> 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;
> >
> >
> > 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 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
>

Re: basic trigger using OLD not working?

From
Ragnar Hafstað
Date:
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

Re: basic trigger using OLD not working?

From
Sven Willenberger
Date:

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

Re: basic trigger using OLD not working? >>THANKS!

From
Rick Casey
Date:
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:

>
>
> 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

Re: basic trigger using OLD not working?

From
Edmund Bacon
Date:
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