Thread: what are the ways to avoid --- "ERROR: EXECUTE of SELECT ... INTO is not implemented yet"

Hello experts,


I am facing this error.

mydb=> select version();
                                            version

--------------------------------------------------------------------------------

 PostgreSQL 8.1.9

(1 row)

mydb=> \i /tmp/test.sql
CREATE FUNCTION
mydb=> select sp_test();
ERROR:  EXECUTE of SELECT ... INTO is not implemented yet
CONTEXT:  PL/pgSQL function "sp_test" line 4 at execute statement


mydb=>
[1]+  Stopped                 su - dbu
mymachine<root># cat /tmp/test.sql
create or replace function sp_test() RETURNS void as $$
declare
        l_var timestamp;
begin
execute 'SELECT NOW() INTO l_var';
end;
$$ language plpgsql;



What are the ways to avoid it
We have an idea of creating the temporary table to store the cursor values,
but this would require a huge effort.

Any alternatives?

~
Shantanu
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, May 30, 2008 at 09:57:49PM +0530, Shantanu wrote:
> Hello experts,
>
>
> I am facing this error.
>
> mydb=> select version();
>                                             version
>
> --------------------------------------------------------------------------------
>
>  PostgreSQL 8.1.9
>
> (1 row)
>
> mydb=> \i /tmp/test.sql
> CREATE FUNCTION
> mydb=> select sp_test();
> ERROR:  EXECUTE of SELECT ... INTO is not implemented yet
> CONTEXT:  PL/pgSQL function "sp_test" line 4 at execute statement
>
>
> mydb=>
> [1]+  Stopped                 su - dbu
> mymachine<root># cat /tmp/test.sql
> create or replace function sp_test() RETURNS void as $$
> declare
>         l_var timestamp;
> begin
> execute 'SELECT NOW() INTO l_var';
  ^^^^^^^^^^^^^^^^^^

Why do you need execute '...' at all?  Why not directly do instead

  SELECT NOW() into l_var;

What is your "real" use case?

Note that if you really need the dynamic command, you might put its
result int a variable, like so:

  EXECUTE 'SELECT NOW()' INTO l_var;

...but if we don't know what you are trying to achieve, it's difficult
to provide meaningful recommendations.

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIQMEjBcgs9XrR2kYRAnaWAJ9Mpu/qvqzi911yiftbe3lLYZiyLgCfcWIl
TKs78mgZiKFRZcHnmHMQnOw=
=I2iD
-----END PGP SIGNATURE-----
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
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 8:38 AM, <tomas@tuxteam.de> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Fri, May 30, 2008 at 09:57:49PM +0530, Shantanu wrote:
> > Hello experts,
> >
> >
> > I am facing this error.
> >
> > mydb=3D> select version();
> >                                             version
> >
> >
> -------------------------------------------------------------------------=
-------
> >
> >  PostgreSQL 8.1.9
> >
> > (1 row)
> >
> > mydb=3D> \i /tmp/test.sql
> > CREATE FUNCTION
> > mydb=3D> select sp_test();
> > ERROR:  EXECUTE of SELECT ... INTO is not implemented yet
> > CONTEXT:  PL/pgSQL function "sp_test" line 4 at execute statement
> >
> >
> > mydb=3D>
> > [1]+  Stopped                 su - dbu
> > mymachine<root># cat /tmp/test.sql
> > create or replace function sp_test() RETURNS void as $$
> > declare
> >         l_var timestamp;
> > begin
> > execute 'SELECT NOW() INTO l_var';
>   ^^^^^^^^^^^^^^^^^^
>
> Why do you need execute '...' at all?  Why not directly do instead
>
>  SELECT NOW() into l_var;
>
> What is your "real" use case?
>
> Note that if you really need the dynamic command, you might put its
> result int a variable, like so:
>
>  EXECUTE 'SELECT NOW()' INTO l_var;
>
> ...but if we don't know what you are trying to achieve, it's difficult
> to provide meaningful recommendations.
>
> Regards
> - -- tom=E1s
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
>
> iD8DBQFIQMEjBcgs9XrR2kYRAnaWAJ9Mpu/qvqzi911yiftbe3lLYZiyLgCfcWIl
> TKs78mgZiKFRZcHnmHMQnOw=3D
> =3DI2iD
> -----END PGP SIGNATURE-----
>
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
>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Sat, May 31, 2008 at 10:51:35PM +0530, Shantanu wrote:
> 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

[...]

> Any alternatives ?

Well -- according to the 8.1 docs (I don't have an 8.1 around at the
moment, sorry), one of my proposals should work:

> On Sat, May 31, 2008 at 8:38 AM, <tomas@tuxteam.de> wrote:
>

[...]

> > Note that if you really need the dynamic command, you might put its
> > result int a variable, like so:
> >
> >  EXECUTE 'SELECT NOW()' INTO l_var;

[...]

(see <http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN>).

Did you try that? Did it work?

Regards
- -- tomás
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFIQ6LIBcgs9XrR2kYRAnDHAJsEz/SS7o/sIwy7dqSGSKpeVhOLlQCfetQM
GSbMIG2s7DUPOIpN6xDKocQ=
=Cb8o
-----END PGP SIGNATURE-----