function doesn't see change in search_path

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: function doesn't see change in search_path
Date: 2011-11-07 14:43:08
Message-ID: 20111107154308.2b363b14@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Broersma 2011-11-07 15:06:18 Re: Custom Contraint Violation Errors
Previous Message Merlin Moncure 2011-11-07 14:28:46 Re: PostgreSQL references in the Middle East