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
