Date: Tue, 8 Jun 2010 15:33:27 +0100
From: Ben Morrow <ben@morrow.me.uk>
To: pgsql-sql@postgresql.org
Subject: Casts in foreign schemas
Message-ID: <20100608143327.GA67823@osiris.mauzo.dyndns.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

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
        $$;
    create cast (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