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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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();
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-08-29 21:56:46 | Re: BUG #3587: EXECUTE and trigger problem |
Previous Message | George Wright | 2007-08-29 19:11:03 | BUG #3586: Time zone problem in SQL query |