Re: what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet" - Mailing list pgsql-bugs

From Shantanu
Subject Re: what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"
Date
Msg-id 97d558890805311022md3d5057le5180eff10ca1c10@mail.gmail.com
Whole thread Raw
In response to Re: what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"  (Craig Ringer <craig@postnewspapers.com.au>)
List pgsql-bugs
Here is my real use case

1) We have nornal SELECT ...INTO calls in the procedure calls
2) However we landed ourselves with the following problem
http://www.postgresql.org/docs/faqs.FAQ.html#item4.19

<snip>
4.19) Why do I get "relation with OID ##### does not exist" errors when
accessing temporary tables in PL/PgSQL functions?

In PostgreSQL versions < 8.3, PL/PgSQL caches function scripts, and an
unfortunate side effect is that if a PL/PgSQL function accesses a temporary
table, and that table is later dropped and recreated, and the function
called again, the function will fail because the cached function contents
still point to the old temporary table. The solution is to use EXECUTE for
temporary table access in PL/PgSQL. This will cause the query to be reparsed
every time.

This problem does not occur in PostgreSQL 8.3 and later.
</snip>

3) So the solution we are trying to implement is the EXECUTE command one.

This is causing the SELECT ...INTO problem


Upgrade is not an option :(

Currently the only available solution is
Use a temporary table where we write the local variable and make it read
from the table .

Any alternatives ?

~
Shantanu

On Sat, May 31, 2008 at 10:13 AM, Craig Ringer <craig@postnewspapers.com.au>
wrote:

> Shantanu wrote:
>
>  ERROR:  EXECUTE of SELECT ... INTO is not implemented yet
>>
>
> PostgreSQL 8.3 includes support for EXECUTE ... INTO, so you may want to
> look at an upgrade.
>
> Personally I wouldn't call this a bug at all. It's just that PostgreSQL
> knew how to parse that query before the support for actually executing it
> was written.
>
> --
> Craig Ringer
>

pgsql-bugs by date:

Previous
From: Shantanu
Date:
Subject: Re: what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"
Next
From: "Netzach"
Date:
Subject: BUG #4218: PGOPTIONS not documented for psql