Thread: Casts in foreign schemas

Is this behaviour expected? (This is with 8.4.3 on FreeBSD.)
   create schema one;   set search_path to one;
   create type foo as (x integer);   create function foo (integer) returns foo       language plpgsql as $$
declare              y foo;           begin               y.x = $1;               return y;           end       $$;
createcast (integer as foo) with function foo (integer);
 
   grant usage on schema one to public;   grant execute on function foo (integer) to public;
   create schema two;
   -- reconnect as a different user
   set search_path to two;   select 3::one.foo;   ERROR:  type "foo" does not exist   CONTEXT:  compilation of PL/pgSQL
function"foo" near line 2
 
   set search_path to two, one;   select 3::foo;    foo   -----    (3)   (1 row)

My understanding of things was that PL/pgSQL functions were compiled at
CREATE FUNCTION time, using the SEARCH_PATH currently in effect. Is that
wrong? Is there some GRANT I'm missing that will make this work?

Ben

Ben Morrow <ben@morrow.me.uk> writes:
> My understanding of things was that PL/pgSQL functions were compiled at
> CREATE FUNCTION time, using the SEARCH_PATH currently in effect. Is that
> wrong?

Yes.  They're compiled at first use within a given session.

If you want to lock down the search path used by a function, it's
possible to do that in recent PG versions, by annotating the function
definition with a "SET search_path = whatever" clause.
        regards, tom lane