The following bug has been logged online:
Bug reference: 3628
Logged by: Pedro Gimeno
Email address: pgsql-001@personal.formauri.es
PostgreSQL version: 8.2.4
Operating system: Linux (Debian stable + backports)
Description: Wrong schema picked
Details:
When a function has a SQL statement to execute that has an unqualified
table, that SQL statement doesn't always pick the table from a schema in the
search_path. The following script is an example:
-- begin script
create database test2;
\c test2
create language 'plpgsql';
create schema schema1;
create schema schema2;
create table schema1.table1 (column1 varchar(10));
create table schema2.table1 (column1 varchar(10));
create function public.f1() returns void as $$
begin
insert into table1 values (current_schema());
end;
$$ language plpgsql;
set search_path=schema1,public;
select f1();
set search_path=schema2,public;
select f1();
select * from schema1.table1;
select * from schema2.table1;
-- end script
The output of the last couple of SELECT statements is:
column1
---------
schema1
schema2
(2 rows)
column1
---------
(0 rows)
while the expected result would be:
column1
---------
schema1
(1 row)
column1
---------
schema2
(1 row)
-- Pedro Gimeno
