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.
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Alvaro Herrera wrote:
<blockquote cite="mid:20070829215646.GW7911@alvh.no-ip.org" type="cite">
<pre wrap="">cyrus wrote:
</pre>
<blockquote type="cite">
<pre wrap="">I am having problems using the Old record as part of the dynamic command
passed to the Execute statement in a trigger.
</pre>
</blockquote>
<pre wrap=""><!---->
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
<a class="moz-txt-link-freetext" href="http://pgfoundry.org/">http://pgfoundry.org/</a> for code that works for this purpose (or something
similar anyway).
</pre>
</blockquote>
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. <br>
<br>
I have attacked my DDL for you to review.<br>
<br>
<pre wrap="">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</a>
PostgreSQL version: 8.1.9
Operating system: i686-redhat-linux-gnu
Description: EXECUTE and trigger problem
</pre>
<br>
</body>
</html>
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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
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.<br>
<br>
thanks<br>
cyrus<br>
<br>
Tom Lane wrote:
<blockquote cite="mid:13161.1188430450@sss.pgh.pa.us" type="cite">
<pre wrap="">Cyrus Downey <a class="moz-txt-link-rfc2396E" href="mailto:cdowney@pryermachine.com"><cdowney@pryermachine.com></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">According to the documentation I should be able to use the OLD record
in the dynamic command passed to the Execute statement.
</pre>
</blockquote>
<pre wrap=""><!---->
The documentation promises no such thing.
regards, tom lane
</pre>
</blockquote>
</body>
</html>