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.klaver@xxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
