Skip site navigation (1) Skip section navigation (2)

BUG #3587: EXECUTE and trigger problem

From: "cyrus" <cdowney(at)pryermachine(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3587: EXECUTE and trigger problem
Date: 2007-08-29 21:02:42
Message-ID: 200708292102.l7TL2gE6080802@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3587
Logged by:          cyrus
Email address:      cdowney(at)pryermachine(dot)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();

Responses

pgsql-bugs by date

Next:From: Alvaro HerreraDate: 2007-08-29 21:56:46
Subject: Re: BUG #3587: EXECUTE and trigger problem
Previous:From: George WrightDate: 2007-08-29 19:11:03
Subject: BUG #3586: Time zone problem in SQL query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group