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
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 |