Re: Problem with temporary tables

On Wednesday 30 June 2010 6:41:18 am Andrea Lombardoni wrote:
> > You need to use EXECUTE for the INSERT statement as well per error:
> >
> > CONTEXT:  SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1,
> >  1, 1)" PL/pgSQL function "test" line 16 at SQL statement
>
> Thanks, this works and solves my problem.
>
> Still, I find this behaviour to be rather quirky.
>
> Ideally the generated query plan should notice such cases and either
> report an error or use the version of the temporary table currently
> 'live'.

In the temporary table case it does for versions of 8.3+. From release notes for
8.3:

"
Automatically re-plan cached queries when table definitions change or statistics
are updated (Tom)

Previously PL/PgSQL functions that referenced temporary tables would fail if the
temporary table was dropped and recreated between function invocations, unless
EXECUTE was used. This improvement fixes that problem and many related issues.
"

>
> At least this quirk should be highlighted both in the plpgsql
> documentation page
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-overview.html
> and linked from the CREATE TABLE page
> http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html

http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

>
> I will propose these changes in the appropriate mailing lists.
>
> Have a nice day!

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com