temp table same name real table

From: "v(dot)brusa(at)joinsnc(dot)com" <v(dot)brusa(at)joinsnc(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: temp table same name real table
Date: 2020-09-30 13:27:17
Message-ID: 30bcf5f9-239c-ced7-1518-1c4709c0e697@joinsnc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


Hi all,
I have this strange behavior when I use temp table with same name of a
permanent table in a function.
Postgres version is:
PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit
I use a query panel from pgAdmin

droptableifexists taba;
createtable taba (cola text);
insertinto taba(cola) values ('val_permanent');

createorreplacefunction test_temp_table(_is_temp
boolean, scan refcursor) RETURNS refcursor AS
$BODY$
BEGIN
if _is_temp then
createtemptable taba oncommitdropasselect * from public.taba withnodata;
insertinto taba(cola) values ('val_temp');
else

endif;

open scan for
select * from taba;

return scan;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;

select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in temp table taba that is 'val_temp'

select test_temp_table(false,'scan'); fetchallfrom scan;
-- return data in permanent table taba that is 'val_permanent'

select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in permanent table taba that is 'val_permanent' and dont see temporary table

If I force a change in search_path the code works correctly.

droptableifexists taba;
createtable taba (cola text);
insertinto taba(cola) values ('val_permanent');

createorreplacefunction test_temp_table(_is_temp
boolean, scan refcursor) RETURNS refcursor AS
$BODY$
BEGIN

if _is_temp then
set search_path=pg_temp,public;
createtemptable taba oncommitdropasselect * from public.taba withnodata;
insertinto taba(cola) values ('val_temp');
else
set search_path=public;
endif;

open scan for
select * from taba;

return scan;

END;
$BODY$
LANGUAGE plpgsql VOLATILE;

select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in temp table taba that is 'val_temp'

select test_temp_table(false,'scan'); fetchallfrom scan;
-- return data in permanent table taba that is 'val_permanent'

select test_temp_table(true,'scan'); fetchallfrom scan;
-- return data in temp table taba that is 'val_temp'

it would seem that whenfunctionreferences permanent
tablefirsttimeinnext calling (select * from taba;)
temptableisnotandisnottrue postgres documentation (Existing permanent
tableswith the same name are not visible to the currentsessionwhile the
temporarytableexists, unless they are referenced withschema-qualified
names)

any idea?

thanks

Vittorio Brusa Zappellini

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wiltsch,Sigrid 2020-09-30 13:32:41 Problem close curser after rollback
Previous Message luis.roberto 2020-09-30 12:11:42 Table sizes