search_path versus dynamic CREATE SCHEMA - Mailing list pgsql-general

Hi folks,

I am curious about why the following doesn't work as expected (tested
on 9.0.3 and HEAD).

CREATE OR REPLACE FUNCTION make_schema(_name text)
RETURNS void LANGUAGE plpgsql VOLATILE AS $$
    DECLARE
        _quoted text;
    BEGIN
        _quoted = quote_ident(_name);
        EXECUTE 'CREATE SCHEMA ' || _quoted;
        EXECUTE 'SET LOCAL search_path TO ' || _quoted;

        CREATE TABLE t (k int primary key);
        INSERT INTO t VALUES (1);
        RETURN;
    END;
$$;

SELECT make_schema('a'), make_schema('b');

I am expecting this script to create two new schemas called 'a' and
'b', each with its own table called 't' containing one row.  This is
what actually happens:

CREATE FUNCTION
psql:../test-dynamic-schema.sql:16: NOTICE:  CREATE TABLE / PRIMARY
KEY will create implicit index "t_pkey" for table "t"
CONTEXT:  SQL statement "CREATE TABLE t (k int primary key)"
PL/pgSQL function "make_schema" line 9 at SQL statement
psql:../test-dynamic-schema.sql:16: NOTICE:  CREATE TABLE / PRIMARY
KEY will create implicit index "t_pkey" for table "t"
CONTEXT:  SQL statement "CREATE TABLE t (k int primary key)"
PL/pgSQL function "make_schema" line 9 at SQL statement
psql:../test-dynamic-schema.sql:16: ERROR:  duplicate key value
violates unique constraint "t_pkey"
DETAIL:  Key (k)=(1) already exists.
CONTEXT:  SQL statement "INSERT INTO t VALUES (1)"
PL/pgSQL function "make_schema" line 10 at SQL statement

It seems that the first call to make_schema succeeds, but the second
fails when it gets to the INSERT.  The duplicate key complaint seems
to suggest that the INSERT statement is resolving t as a.t, instead of
the newly created b.t.  But how is that possible?  As far as I can
see, the INSERT should be using the same search_path as the CREATE
TABLE, which would have failed with "table already exists" if 'a' was
at the front of the search_path, no?

Cheers,
BJ