Re: SELECT INTO Troubles

Brian Troxell <trox(at)mindspring(dot)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