Thread: SELECT INTO Troubles

Hello again everyone. This is the next message in my series of troubles
regarding EXECUTE and SELECT INTO (or CREATE TABLE). Hopefully the list
will be as helpful and informative as with my previous queries.

I have a PG/plSQL function get_attribute()) that does a simple lookup
using this code:
EXECUTE ''CREATE TEMPORARY TABLE random_tab (item) AS SELECT '' ||
       quote_ident(v_column) || '' FROM '' ||
       quote_ident(v_table_name) || '' WHERE '' || v_key_sql;
v_return := random_tab.item;
raise NOTICE ''v_return = %'', v_return;
DROP TABLE random_tab;

In my test routine, I call this get_attribute() function, followed by
another set_attribute() function, followed by get_attribute() again to
make sure the value was changed.

The problem is with the second call to get_attribute()...I get this
error:
psql:lop2:15: ERROR:  Relation 74176 does not exist

But if I remove the DROP TABLE statement from the get_attribute()
function, the second call to it fails with this error:
psql:lop2:15: ERROR:  Relation 'random_tab' already exists

So I tried it again, taking out the EXECUTE and just hard-coding the
SELECT statement, and get the same errors.

This sounds like a total Catch-22 to me, which must mean I'm doing
something wrong. Help!!!!!

Thanks in advance!

Brian Troxell
trox@mindspring.com

Brian Troxell <trox@mindspring.com> writes:
> I have a PG/plSQL function get_attribute()) that does a simple lookup
> using this code:
> EXECUTE ''CREATE TEMPORARY TABLE random_tab (item) AS SELECT '' ||
>        quote_ident(v_column) || '' FROM '' ||
>        quote_ident(v_table_name) || '' WHERE '' || v_key_sql;
> v_return := random_tab.item;
> raise NOTICE ''v_return = %'', v_return;
> DROP TABLE random_tab;

You can't do it like that, because that breaks exactly the thing you
are trying to get around, namely that non-EXECUTE queries in plpgsql
are planned only once and the plan is cached.  So both of the lines
        v_return := random_tab.item;
        DROP TABLE random_tab;
(the first of which is implicitly a SELECT, remember) are going to
fail on second and later iterations, because the cached plan refers
to a table that ain't there anymore.

While you can easily turn the DROP into EXECUTE ''DROP ...'', I'm
not sure I see what to do about the other thing.  You might have to
give up and write the function in pltcl or plperl, which don't try
to cache query plans (with the obvious implications for speed vs.
flexibility).

            regards, tom lane