-----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-----


Tue, 14 Sep 2004 20:34:32 +0200
Gaetano Mendola <mendola@bigfoot.com>
NEW used in a query that is not in a rule
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 to God must believe that he is, and 
      that he is a rewarder of them that diligently seek 
      him."        Hebrews 11:6 


Tue, 14 Sep 2004 21:59:17 +0100
Oliver Elphick <olly@lfix.co.uk>
Re: NEW used in a query that is not in a rule
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


Tue, 14 Sep 2004 23:57:03 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: NEW used in a query that is not in a rule