<div class="gmail_quote">On 17 June 2010 12:31, Jean-Baptiste Quenot <span dir="ltr"><<a
href="mailto:jbq@caraldi.com">jbq@caraldi.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:
0pt0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;"> Dear hackers,<br /><br /> I have a
prettynasty problem to submit to your careful scrutiny.<br /><br /> Please consider the following piece of SQL code:<br
/><br/><br /> CREATE SCHEMA bar;<br /> SET search_path = bar;<br /><br /> CREATE FUNCTION bar() RETURNS text AS $$<br
/>BEGIN<br /> RETURN 'foobar';<br /> END<br /> $$ LANGUAGE plpgsql IMMUTABLE;<br /><br /> CREATE SCHEMA foo;<br />
SETsearch_path = foo;<br /><br /> CREATE FUNCTION foo() RETURNS text AS $$<br /> BEGIN<br /> RETURN bar();<br />
END<br/> $$ LANGUAGE plpgsql IMMUTABLE;<br /><br /> SET search_path = public;<br /><br /> CREATE TABLE foobar (d
text);<br/> insert into foobar (d) values ('foobar');<br /><br /> set search_path = public, foo, bar;<br /> CREATE
INDEXfoobar_d on foobar using btree(foo());<br /><br /><br /> Run this on a newly created database, and dump it with
pg_dump.You'll<br /> notice that the dump is unusable. Creating a new database from this<br /> dump will trigger the
followingerror:<br /><br /> ERROR: function bar() does not exist<br /> LINE 1: SELECT bar()<br /> ^<br
/>HINT: No function matches the given name and argument types. You<br /> might need to add explicit type casts.<br />
QUERY: SELECT bar()<br /> CONTEXT: PL/pgSQL function "foo" line 2 at RETURN<br /><br /> How can we fix this?<br />
--<br/> Jean-Baptiste Quenot<br /><font color="#888888"><br /> --</font><br /></blockquote></div><br />I think Postgres
doesn'tcheck to see whether bar() exists in the current search path when you create the foo() function, and since it
isn'tin the foo() function's search path value, it fails to find the function when you try to use it. It can probably
befixed (this specific case, not generally) with:<br /><br />ALTER FUNCTION foo.foo() SET search_path=foo, bar;<br
/><br/>Thom<br />