On 17 June 2010 12:31, Jean-Baptiste Quenot <j...@caraldi.com> wrote:
> Dear hackers,
>
> I have a pretty nasty problem to submit to your careful scrutiny.
>
> Please consider the following piece of SQL code:
>
>
> CREATE SCHEMA bar;
> SET search_path = bar;
>
> CREATE FUNCTION bar() RETURNS text AS $$
> BEGIN
> RETURN 'foobar';
> END
> $$ LANGUAGE plpgsql IMMUTABLE;
>
> CREATE SCHEMA foo;
> SET search_path = foo;
>
> CREATE FUNCTION foo() RETURNS text AS $$
> BEGIN
> RETURN bar();
> END
> $$ LANGUAGE plpgsql IMMUTABLE;
>
> SET search_path = public;
>
> CREATE TABLE foobar (d text);
> insert into foobar (d) values ('foobar');
>
> set search_path = public, foo, bar;
> CREATE INDEX foobar_d on foobar using btree(foo());
>
>
> Run this on a newly created database, and dump it with pg_dump. You'll
> notice that the dump is unusable. Creating a new database from this
> dump will trigger the following error:
>
> ERROR: function bar() does not exist
> LINE 1: SELECT bar()
> ^
> HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> QUERY: SELECT bar()
> CONTEXT: PL/pgSQL function "foo" line 2 at RETURN
>
> How can we fix this?
> --
> Jean-Baptiste Quenot
>
> --
>
I think Postgres doesn't check to see whether bar() exists in the current
search path when you create the foo() function, and since it isn't in the
foo() function's search path value, it fails to find the function when you
try to use it. It can probably be fixed (this specific case, not generally)
with:
ALTER FUNCTION foo.foo() SET search_path=foo, bar;
Thom
