Re: [GENERAL] temp tables in functions?
On Thu, Feb 08, 2007 at 12:29:37PM +0500, Shoaib Mir wrote: Something like this will help you: execute immediate 'create temporary table test (a number) on commit drop'; PL/pgSQL doesn't recognize immediate and number isn't a PostgreSQL type so the above yields a syntax error. Also, EXECUTE isn't necessary for the CREATE TABLE statement, although as Bruno mentioned EXECUTE will be necessary for other statements due to plan caching. And ON COMMIT DROP won't help if you call the function multiple times in the same transaction. -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] temp tables in functions?
Agreed :) I guess missed out some details from there as I just thought he needed to drop a temp table inside a function like this: CREATE OR REPLACE function tempfunc (int) returns int AS $$ begin execute 'create temporary table test (a numeric) on commit drop'; execute 'INSERT INTO test values (1);'; return 1; end; $$ LANGUAGE 'plpgsql' used number by mistake so sorry for any inconvenience caused as I was trying it with EnterpriseDB (where 'number 'is added for Oracle compatibility) -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/8/07, Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Feb 08, 2007 at 12:29:37PM +0500, Shoaib Mir wrote: Something like this will help you: execute immediate 'create temporary table test (a number) on commit drop'; PL/pgSQL doesn't recognize immediate and number isn't a PostgreSQL type so the above yields a syntax error. Also, EXECUTE isn't necessary for the CREATE TABLE statement, although as Bruno mentioned EXECUTE will be necessary for other statements due to plan caching. And ON COMMIT DROP won't help if you call the function multiple times in the same transaction. -- Michael Fuhr
[GENERAL] temp tables in functions?
Having developed a complex query, I want to wrap it up as a function so that it can take a parameter and return a set of rows. This query is currently written as multiple sql statements that create a few interstitial temp tables that are then joined. If I put this into a function definition, do those temp tables get dropped automatically when the function returns? My alternative is to re-write the query as a self joins and subqueries. I can do that, but it's somewhat less readable. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] temp tables in functions?
On Wed, Feb 07, 2007 at 20:40:09 -0800, jws [EMAIL PROTECTED] wrote: Having developed a complex query, I want to wrap it up as a function so that it can take a parameter and return a set of rows. This query is currently written as multiple sql statements that create a few interstitial temp tables that are then joined. If I put this into a function definition, do those temp tables get dropped automatically when the function returns? See: http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html So, the answer is no. Also note that currently Postgres will cache information about tables used in functions and this may not work well when you are dropping and recreating tables with the same name in the same session. For that kind of thing you need to use EXECUTE to avoid caching. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] temp tables in functions?
Something like this will help you: execute immediate 'create temporary table test (a number) on commit drop'; -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 2/8/07, Bruno Wolff III [EMAIL PROTECTED] wrote: On Wed, Feb 07, 2007 at 20:40:09 -0800, jws [EMAIL PROTECTED] wrote: Having developed a complex query, I want to wrap it up as a function so that it can take a parameter and return a set of rows. This query is currently written as multiple sql statements that create a few interstitial temp tables that are then joined. If I put this into a function definition, do those temp tables get dropped automatically when the function returns? See: http://www.postgresql.org/docs/8.2/interactive/sql-createtable.html So, the answer is no. Also note that currently Postgres will cache information about tables used in functions and this may not work well when you are dropping and recreating tables with the same name in the same session. For that kind of thing you need to use EXECUTE to avoid caching. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
