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
drop table if exists taba;
create table taba (cola text);
insert into taba(cola) values ('val_permanent');
create or replace function test_temp_table(_is_temp boolean, scan refcursor) RETURNS refcursor AS
$BODY$
BEGIN
if _is_temp then
create temp table taba on commit drop as select * from public.taba with no data;
insert into taba(cola) values ('val_temp');
else
end if;
open scan for
select * from taba;
return scan;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
select test_temp_table(true,'scan'); fetch all from scan; -- return data in temp table taba that is 'val_temp'
select test_temp_table(false,'scan'); fetch all from scan; -- return data in permanent table taba that is 'val_permanent'
select test_temp_table(true,'scan'); fetch all from 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.
drop table if exists taba;
create table taba (cola text);
insert into taba(cola) values ('val_permanent');
create or replace function test_temp_table(_is_temp boolean, scan refcursor) RETURNS refcursor AS
$BODY$
BEGIN
if _is_temp then
set search_path=pg_temp,public;
create temp table taba on commit drop as select * from public.taba with no data;
insert into taba(cola) values ('val_temp');
else
set search_path=public;
end if;
open scan for
select * from taba;
return scan;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
select test_temp_table(true,'scan'); fetch all from scan; -- return data in temp table taba that is 'val_temp'
select test_temp_table(false,'scan'); fetch all from scan; -- return data in permanent table taba that is 'val_permanent'
select test_temp_table(true,'scan'); fetch all from scan; -- return data in temp table taba that is 'val_temp'
it would seem that when function references permanent table first time in next calling (select * from taba;) temp table is not and is not true postgres documentation (Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names)
any idea?
thanks
Vittorio Brusa Zappellini