Thread: NEW used in a query that is not in a rule
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi all,
I'm bouncing on the following problem, I don't know if is a bug or if exist a different
way to do it.
The following code is not meaningfull but it's an extract of what I'm trying to do:
CREATE TABLE foo ( field1 INTEGER );
CREATE OR REPLACE FUNCTION trigger_foo()
RETURNS TRIGGER AS'
DECLARE
~ my_field TEXT;
~ my_stat TEXT;
BEGIN
~ my_field = TG_ARGV[0];
~ my_stat = ''SELECT field1 FROM foo where field1 = NEW.'' || my_field;
~ EXECUTE my_stat;
~ RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER check_foo
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE trigger_foo('field1');
insert into foo values ( 3 );
I got the error in the subject, it's like the EXECUTE open another contest and
forget that is inside a rule.
Any idea someone ?
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBRzm37UpzwH2SGd4RAnObAKD4G6S6MdvaYsGxjS88sn+u2OJqagCg86ut
tsa/AXBfKtB12sCPBIwJAYc=
=G2DY
-----END PGP SIGNATURE-----
I am also don't know how use NEW,OLD in plpgsql
but in pltcl possible to use $NEW($my_field), $OLD($my_field)
--
Vadim Passynkov
-----Original Message-----
From: Gaetano Mendola [mailto:mendola@bigfoot.com]
Sent: Tuesday, September 14, 2004 2:35 PM
To: pgsql-hackers@postgresql.org
Subject: [HACKERS] NEW used in a query that is not in a rule
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi all,
I'm bouncing on the following problem, I don't know if is a bug or if exist
a different
way to do it.
The following code is not meaningfull but it's an extract of what I'm trying
to do:
CREATE TABLE foo ( field1 INTEGER );
CREATE OR REPLACE FUNCTION trigger_foo()
RETURNS TRIGGER AS'
DECLARE
~ my_field TEXT;
~ my_stat TEXT;
BEGIN
~ my_field = TG_ARGV[0];
~ my_stat = ''SELECT field1 FROM foo where field1 = NEW.'' || my_field;
~ EXECUTE my_stat;
~ RETURN NEW;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER check_foo
BEFORE INSERT ON foo
FOR EACH ROW EXECUTE PROCEDURE trigger_foo('field1');
insert into foo values ( 3 );
I got the error in the subject, it's like the EXECUTE open another contest
and
forget that is inside a rule.
Any idea someone ?
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFBRzm37UpzwH2SGd4RAnObAKD4G6S6MdvaYsGxjS88sn+u2OJqagCg86ut
tsa/AXBfKtB12sCPBIwJAYc=
=G2DY
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
On Tue, 2004-09-14 at 19:34, Gaetano Mendola wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi all,
> I'm bouncing on the following problem, I don't know if is a bug or if exist a different
> way to do it.
> The following code is not meaningfull but it's an extract of what I'm trying to do:
>
>
> CREATE TABLE foo ( field1 INTEGER );
>
> CREATE OR REPLACE FUNCTION trigger_foo()
> RETURNS TRIGGER AS'
> DECLARE
>
> ~ my_field TEXT;
> ~ my_stat TEXT;
>
> BEGIN
>
> ~ my_field = TG_ARGV[0];
>
> ~ my_stat = ''SELECT field1 FROM foo where field1 = NEW.'' || my_field;
My guess is that you are having this problem because you are executing a
query referring to NEW rather than using it directly.
I don't think you can refer to NEW in a command string given to
EXECUTE. You probably need to set up an IF...ELSIF...ELSE...END IF
structure to get the value to put into the command string.
> ~ EXECUTE my_stat;
>
> ~ RETURN NEW;
>
>
> END;
> ' LANGUAGE 'plpgsql';
>
>
> CREATE TRIGGER check_foo
> BEFORE INSERT ON foo
> FOR EACH ROW EXECUTE PROCEDURE trigger_foo('field1');
>
> insert into foo values ( 3 );
>
>
> I got the error in the subject, it's like the EXECUTE open another contest and
> forget that is inside a rule.
>
> Any idea someone ?
>
>
>
>
> Regards
> Gaetano Mendola
>
>
>
>
>
>
>
>
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.2.4 (MingW32)
> Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
> iD8DBQFBRzm37UpzwH2SGd4RAnObAKD4G6S6MdvaYsGxjS88sn+u2OJqagCg86ut
> tsa/AXBfKtB12sCPBIwJAYc=
> =G2DY
> -----END PGP SIGNATURE-----
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
======================================== "But without faith it is impossible to please him; for he that cometh
toGod must believe that he is, and that he is a rewarder of them that diligently seek him." Hebrews
11:6
Oliver Elphick wrote: > >> My guess is that you are having this problem because you are executing a >> query referring to NEW rather than using it directly. This for sure, I'm able to use NEW directly but I don't know at definition time wich field of NEW I have to use. >> I don't think you can refer to NEW in a command string given to >> EXECUTE. You probably need to set up an IF...ELSIF...ELSE...END IF >> structure to get the value to put into the command string. I can't because the combination are not a limited number. Regards Gaetano Mendola