Re: search_path versus dynamic CREATE SCHEMA

Brendan Jurd <direvus@gmail.com> writes:
> 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;
> $$;

> 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?

The CREATE TABLE is a utility statement, which has no plan to cache;
but the INSERT is a plannable statement, so it caches a plan that
references a.t.  There has been debate before about whether or how to
change that behavior ...

            regards, tom lane