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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
