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 [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 06 February 2001 16:16
To: Michael Ansley
Cc: Jan Wieck; sqllist; pgsql-hackers(at)postgresql(dot)org
Subject: Re: [SQL] PL/PGSQL function with parameters

Michael Ansley <Michael(dot)Ansley(at)intec-telecom-systems(dot)com> 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
**********************************************************************