Re: ALTER OBJECT any_name SET SCHEMA name

On 31.10.2010 21:42, Dimitri Fontaine wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> If I understand that correctly, the idea is that p_fun holds the name of a
>> function that's in the same schema as the extension? You would write that as
>>
>> v_sql := 'SELECT * FROM @extschema(at)(dot)' || p_fun || '()';
>
> Fair enough. Now what about the citext example, where IIRC the failure
> is not on function names but operators and opclass not found, etc.

Just do "SET search_path=(at)extschema@" at the beginning of the install
script, just like we have "SET search_path=public" there now.

> Forcing extension's author to get to always use the following notation
> seems to me like pushing it:
>
> - WHERE foo = bar
> + WHERE foo operator(@extschema(at)(dot)=) bar
>
> Also, those examples are plpgsql but extensions are free to depend on
> plperl or plpython, or even some pljs or plscheme out there.

Well, in case of functions you can always do "CREATE FUNCTION ... AS $$
... $$ SET search_path=(at)extschema".

"ALTER ... SET SCHEMA" wouldn't do anything for SQL statements embedded
in plperl or plpython anyway.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com