pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCasts in foreign schemas
2+ messages / 2 participants
[nested] [flat]
* Casts in foreign schemas
@ 2010-06-08 14:33 Ben Morrow <ben@morrow.me.uk>
2010-06-08 14:50 ` Re: Casts in foreign schemas Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Ben Morrow @ 2010-06-08 14:33 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Casts in foreign schemas
2010-06-08 14:33 Casts in foreign schemas Ben Morrow <ben@morrow.me.uk>
@ 2010-06-08 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2010-06-08 14:50 UTC (permalink / raw)
To: Ben Morrow <ben@morrow.me.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-06-08 14:50 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-06-08 14:33 Casts in foreign schemas Ben Morrow <ben@morrow.me.uk>
2010-06-08 14:50 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox