Thread: creating a temp table in a function
I have a function that creates a temp table and drops it on commit. If I run the function twice in the same psql interactive session, I get an error. If I run it twice in two different psql sessions (using the -c flag), I get no error. Is this expected behavior? If so, why?
You are now connected to database "test".
test=# CREATE OR REPLACE FUNCTION test_function() RETURNS void AS $t$
test$# BEGIN
test$# create temp table my_temp_table(id bigint) on commit drop;
test$# insert into my_temp_table values(0);
test$# END;
test$# $t$ LANGUAGE plpgsql;
CREATE FUNCTION
test=# select test_function();
test_function
---------------
(1 row)
test=# select test_function();
ERROR: relation with OID 70828339 does not exist
CONTEXT: SQL statement "INSERT INTO my_temp_table values(0)"
PL/pgSQL function "test_function" line 3 at SQL statement
test=# \q
[shahaf@staging-query-1 ~]$ psql -U postgres -d test -c "select test_function()"
Password for user postgres:
test_function
---------------
(1 row)
[shahaf@staging-query-1 ~]$ psql -U postgres -d test -c "select test_function()"
Password for user postgres:
test_function
---------------
(1 row)
Shahaf Abileah | Lead Software Developer - Data Team
shahaf@redfin.com | tel: 206.859.2869 | cell: 206.331.2057 | www.redfin.com
On Fri, 4 Apr 2008 09:13:18 -0700 Shahaf Abileah wrote:
> I have a function that creates a temp table and drops it on commit. If
> I run the function twice in the same psql interactive session, I get an
> error. If I run it twice in two different psql sessions (using the -c
> flag), I get no error. Is this expected behavior? If so, why?
Create a query in a text string and run the query with EXECUTE.
In your case the OIDs are determined during parsing, but in the next
function call the temp table is gone away. So you have to make sure the
table is dynamically created.
Kind regards
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
On Fri, 4 Apr 2008 09:13:18 -0700 "Shahaf Abileah" <shahaf@redfin.com> wrote: > I have a function that creates a temp table and drops it on > commit. If I run the function twice in the same psql interactive > session, I get an error. If I run it twice in two different psql > sessions (using the -c flag), I get no error. Is this expected > behavior? If so, why? http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 ? -- Ivan Sergio Borgonovo http://www.webthatworks.it
On 04/04/2008 20:47, Andreas 'ads' Scherbaum wrote: >> I have a function that creates a temp table and drops it on commit. If >> I run the function twice in the same psql interactive session, I get an >> error. If I run it twice in two different psql sessions (using the -c >> flag), I get no error. Is this expected behavior? If so, why? > > Create a query in a text string and run the query with EXECUTE. Wasn't this fixed in 8.3? - Is it not the case that you no longer have to handle temp tables in plpgsql functions via EXECUTE? Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Raymond O'Donnell wrote: > On 04/04/2008 20:47, Andreas 'ads' Scherbaum wrote: > >>> I have a function that creates a temp table and drops it on commit. If >>> I run the function twice in the same psql interactive session, I get an >>> error. If I run it twice in two different psql sessions (using the -c >>> flag), I get no error. Is this expected behavior? If so, why? >> >> Create a query in a text string and run the query with EXECUTE. > > Wasn't this fixed in 8.3? - Is it not the case that you no longer have > to handle temp tables in plpgsql functions via EXECUTE? Yes, it should work on 8.3. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, 04 Apr 2008 21:22:17 +0100 Raymond O'Donnell wrote:
> On 04/04/2008 20:47, Andreas 'ads' Scherbaum wrote:
>
> >> I have a function that creates a temp table and drops it on commit. If
> >> I run the function twice in the same psql interactive session, I get an
> >> error. If I run it twice in two different psql sessions (using the -c
> >> flag), I get no error. Is this expected behavior? If so, why?
> >
> > Create a query in a text string and run the query with EXECUTE.
>
> Wasn't this fixed in 8.3? - Is it not the case that you no longer have
> to handle temp tables in plpgsql functions via EXECUTE?
Uhm, every day you learn something new.
Thanks for updating me, guys.
Kind regards
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors