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