Re: ALTER OBJECT any_name SET SCHEMA name

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> How about something like:
>
> CREATE EXTENSION myextension ... SCHEMA myschema;
>
> And in the .sql file in the extension you could have special markers for the
> schema, something like:
>
> CREATE FUNCTION otherfunction() AS ...;
> CREATE FUNCTION foo() AS $$ SELECT 'foo', @extschema(at)(dot)otherfunction() $$;
>
> @extschema@ would be search&replaced at CREATE EXTENSION time with the
> schema specified by the user.

Please find attached v12 of the patch, which implements that idea.

And a new pg_execute_from_file patch version too: the function now has a
second (documented) variant accepting a VARIADIC text[] argument where
to put pairs of name and value for the placeholders in the script.

I guess it would be cleaner with hstore in core, but we're not there
yet, so meanwhile it's a variable length array.

The CREATE EXTENSION ... WITH SCHEMA ... command will then use the
variadic form of pg_execute_from_file() with a single variable in there,
the proposed @extschema(at)(dot) When the option is not used, the placeholder
is still set, hard-coded to 'public'.

Contrib scripts have been all changed this way:

- SET search_path = public;
+ SET search_path = @extschema@;

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support