Received: from mail.postgresql.org (webmail.postgresql.org [216.126.85.28]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id f17DVRx48430 for ; Wed, 7 Feb 2001 08:31:27 -0500 (EST) (envelope-from pgsql-hackers-owner@postgresql.org) Received: from fileserver002.intecsystems.co.uk ([194.200.14.125]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f16GOex08383; Tue, 6 Feb 2001 11:24:40 -0500 (EST) (envelope-from Michael.Ansley@intec-telecom-systems.com) Received: from fileserver002.intecsystems.co.uk (unverified) by fileserver002.intecsystems.co.uk (Content Technologies SMTPRS 4.1.5) with ESMTP id ; Tue, 6 Feb 2001 16:28:05 +0000 Received: by FILESERVER002 with Internet Mail Service (5.5.2650.21) id <1M6DXCP6>; Tue, 6 Feb 2001 16:28:05 -0000 Message-ID: <7F124BC48D56D411812500D0B747251480F3D6@FILESERVER002> From: Michael Ansley To: "'Tom Lane'" Cc: Jan Wieck , sqllist , pgsql-hackers@postgresql.org Subject: RE: [SQL] PL/PGSQL function with parameters Date: Tue, 6 Feb 2001 16:27:56 -0000 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: multipart/alternative ; boundary="----_=_NextPart_001_01C09059.C80DECBE" X-Archive-Number: 200102/224 X-Sequence-Number: 4355 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C09059.C80DECBE Content-Type: text/plain; charset="iso-8859-1" Yes, that was why I wrote it in the way that I did. The table is effectively given a constant name, and the count is got from the table with a known name. But of a kludge, but in 45sec, that was all I could come up with ;-) It would be VERY useful to see it fixed. Cheers... MikeA -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: 06 February 2001 16:16 To: Michael Ansley Cc: Jan Wieck; sqllist; pgsql-hackers@postgresql.org Subject: Re: [SQL] PL/PGSQL function with parameters Michael Ansley writes: > CREATE FUNCTION table_count(varchar) RETURNS integer AS ' > DECLARE > SQL varchar; > RES integer; > BEGIN > SQL = ''SELECT * INTO temp1 FROM '' || $1; > EXECUTE SQL; > SELECT count(*) INTO RES FROM temp1; > RETURN(RES) > END; > ' > LANGUAGE 'plpgsql'; > What I couldn't get it to do was to select directly into the variable RES. I tried this, and it seems that "SELECT ... INTO foo" is not executed correctly by EXECUTE --- the INTO is handled as an ordinary select-into- table construct rather than plpgsql's select-into-variable. While I have not looked closely, I seem to recall that plpgsql handles INTO by stripping that clause out of the statement before it's passed to the SQL engine. Evidently that's not happening in the EXECUTE case. Jan, do you agree this is a bug? Is it reasonable to try to repair it for 7.1? If we do not change the behavior of EXECUTE now, I fear it will be too late --- some people will come to depend on the existing behavior. regards, tom lane ********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com ********************************************************************** ------_=_NextPart_001_01C09059.C80DECBE Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: [SQL] PL/PGSQL function with parameters

Yes, that was why I wrote it in the way that I did. = The table is effectively given a constant name, and the count is got from = the table with a known name.  But of a kludge, but in 45sec, that was = all I could come up with ;-)

It would be VERY useful to see it fixed.

Cheers...


MikeA
 

-----Original Message-----
From: Tom Lane [mai= lto:tgl@sss.pgh.pa.us]
Sent: 06 February 2001 16:16
To: Michael Ansley
Cc: Jan Wieck; sqllist; pgsql-hackers@postgresql.org
Subject: Re: [SQL] PL/PGSQL function with parameters


Michael Ansley <Michael.Ansley@intec-telecom-systems.c= om> writes:
> CREATE FUNCTION table_count(varchar) RETURNS intege= r AS '
> DECLARE
>       SQL varchar;
>       RES integer;
> BEGIN
>       SQL =3D ''SELECT * I= NTO temp1 FROM '' || $1;
>       EXECUTE SQL;
>       SELECT count(*) INTO= RES FROM temp1;
>       RETURN(RES)
> END;
> '
> LANGUAGE 'plpgsql';

> What I couldn't get it to do was to select directly = into the variable RES.

I tried this, and it seems that "SELECT ... INTO foo= " is not executed
correctly by EXECUTE --- the INTO is handled as an ordin= ary select-into-
table construct rather than plpgsql's select-into-variab= le.

While I have not looked closely, I seem to recall that pl= pgsql handles
INTO by stripping that clause out of the statement befor= e it's passed to
the SQL engine.  Evidently that's not happening in = the EXECUTE case.

Jan, do you agree this is a bug?  Is it reasonable t= o try to repair it
for 7.1?  If we do not change the behavior of EXECU= TE now, I fear it
will be too late --- some people will come to depend on = the existing
behavior.

             = ;           reg= ards, tom lane



**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.

www.mimesweeper.com
**********************************************************************
------_=_NextPart_001_01C09059.C80DECBE--