Re: search_path versus dynamic CREATE SCHEMA

Brendan Jurd <direvus(at)gmail(dot)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