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(at)staging-query-1 ~]$ psql -U postgres -d test -c "select
test_function()"

Password for user postgres:

test_function

---------------

(1 row)

[shahaf(at)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(at)redfin(dot)com <mailto:shahaf(at)redfin(dot)com> | tel: 206.859.2869 |
cell: 206.331.2057 | www.redfin.com <http://www.redfin.com>