function doesn't see change in search_path - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject function doesn't see change in search_path
Date
Msg-id 20111107154308.2b363b14@dawn.webthatworks.it
Whole thread Raw
Responses Re: function doesn't see change in search_path  (Richard Huxton <dev@archonet.com>)
List pgsql-general
I have a behaviour similar to this
http://archives.postgresql.org/pgsql-bugs/2007-09/msg00017.php

create language plpgsql;

create schema test1;
create schema test2;
create table test1.a(a varchar(3) unique);
create table test2.a(a varchar(3) unique);

create or replace function test_insert() returns void as
$$
begin
    raise notice 'path %', current_schemas(true);
    insert into a values('a');
end;
$$ language plpgsql volatile;


set search_path to 'test1', 'public';

select * from test_insert();
NOTICE:  path {pg_catalog,test1,public}
 test_insert
-------------

(1 row)

set search_path to 'test2', 'public';

select * from test_insert();
NOTICE:  path {pg_catalog,test2,public}
ERROR:  duplicate key value violates unique constraint "a_a_key"
CONTEXT:  SQL statement "insert into a values('a')"
PL/pgSQL function "test_insert" line 3 at SQL statement

PostgreSQL 8.3.14

what's going on?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it


pgsql-general by date:

Previous
From: Merlin Moncure
Date:
Subject: Re: PostgreSQL references in the Middle East
Next
From: Richard Broersma
Date:
Subject: Re: Custom Contraint Violation Errors