Thread: BUG #3587: EXECUTE and trigger problem

The following bug has been logged online:

Bug reference:      3587
Logged by:          cyrus
Email address:      cdowney@pryermachine.com
PostgreSQL version: 8.1.9
Operating system:   i686-redhat-linux-gnu
Description:        EXECUTE and trigger problem
Details:

I am having problems using the Old record as part of the dynamic command
passed to the Execute statement in a trigger.

The error recived was:
ERROR:  OLD used in query that is not in a rule
CONTEXT:  SQL statement "INSERT INTO public.test_hist  SELECT  OLD.* from
OLD;"
PL/pgSQL function "hist_insert_tr" line 8 at execute statement

Below is the DDL I used to replicate the problem.

create table public.test(itest integer);
create table public.test_hist(      itest integer,
                             "dmodified" TIMESTAMP WITHOUT TIME ZONE DEFAULT
now() NOT NULL
                             );


CREATE OR REPLACE FUNCTION public.hist_insert_tr () RETURNS trigger AS
$body$
declare
       lcDynamicSQL varchar := '';
BEGIN
       --does not work
       --lcDynamicSQL := 'INSERT INTO public.' || TG_ARGV[0] || '_hist
SELECT  OLD.*;';
       --does not work
       lcDynamicSQL := 'INSERT INTO public.' || TG_ARGV[0] || '_hist  SELECT
 OLD.* from OLD;';
       EXECUTE lcDynamicSQL;
       RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


CREATE TRIGGER test_after_tr AFTER UPDATE
ON public.test FOR EACH ROW
EXECUTE PROCEDURE public.hist_insert_tr(test);



insert into test (itest) VALUES(1);
update test set itest = 2;



drop table public.test cascade;
drop table public.test_hist;
DROP FUNCTION public.hist_insert_tr();

cyrus wrote:

> I am having problems using the Old record as part of the dynamic command
> passed to the Execute statement in a trigger.

This is not a bug (please don't use the bug report form for support
requests).

Anyway, you may want to check the "tablelog" project in
http://pgfoundry.org/ for code that works for this purpose (or something
similar anyway).


--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:

  cyrus wrote:



    I am having problems using the Old record as part of the dynamic command
passed to the Execute statement in a trigger.



This is not a bug (please don't use the bug report form for support
requests).

Anyway, you may want to check the "tablelog" project in
http://pgfoundry.org/ for code that works for this purpose (or something
similar anyway).




This was not a support request.  I have already solved the problem, but
it much more code than my original method (which does not work). 
According to the documentation I should be able to use the OLD record
in the dynamic command passed to the Execute statement.  

I have attacked my DDL for you to review.

The error received was:
ERROR:  OLD used in query that is not in a rule
CONTEXT:  SQL statement "INSERT INTO public.test_hist  SELECT  OLD.* from
OLD;"
PL/pgSQL function "hist_insert_tr" line 8 at execute statement

Below is the DDL I used to replicate the problem.

create table public.test(itest integer);
create table public.test_hist(      itest integer,
                             "dmodified" TIMESTAMP WITHOUT TIME ZONE DEFAULT
now() NOT NULL
                             );


CREATE OR REPLACE FUNCTION public.hist_insert_tr () RETURNS trigger AS
$body$
declare
       lcDynamicSQL varchar := '';
BEGIN
       --does not work
       --lcDynamicSQL := 'INSERT INTO public.' || TG_ARGV[0] || '_hist
SELECT  OLD.*;';
       --does not work
       lcDynamicSQL := 'INSERT INTO public.' || TG_ARGV[0] || '_hist  SELECT
 OLD.* from OLD;';
       EXECUTE lcDynamicSQL;
       RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;


CREATE TRIGGER test_after_tr AFTER UPDATE
ON public.test FOR EACH ROW
EXECUTE PROCEDURE public.hist_insert_tr(test);



insert into test (itest) VALUES(1);
update test set itest = 2;



drop table public.test cascade;
drop table public.test_hist;
DROP FUNCTION public.hist_insert_tr();

Bug reference:      3587
Logged by:          cyrus
Email address:      <a class="moz-txt-link-abbreviated"
 href="mailto:cdowney@pryermachine.com">cdowney@pryermachine.com
PostgreSQL version: 8.1.9
Operating system:   i686-redhat-linux-gnu
Description:        EXECUTE and trigger problem

Cyrus Downey <cdowney@pryermachine.com> writes:
> According to the documentation I should be able to use the OLD record
> in the dynamic command passed to the Execute statement.

The documentation promises no such thing.

            regards, tom lane

but the documentation does not say I shouldn't be able to do it. 
Additionally, an alternate method (which was not included) using
temporary tables works fine.  It fails when switching to the old record.

thanks
cyrus

Tom Lane wrote:

  Cyrus Downey <cdowney@pryermachine.com> writes:


    According to the documentation I should be able to use the OLD record
in the dynamic command passed to the Execute statement.



The documentation promises no such thing.

            regards, tom lane