As discussed in irc://freenode/postgresql (2010-05-06 16:20)
Function bodies are checked using the _current_ search_path instead of
the search_path supplied by the "SET search_path" clause.
This leads to wrong error messages during creation.
On the other hand it can make a function body pass the check, even
though it will raise an error is use.
Proposed solution: Function bodies should be checked with the
search_path provided by "SET search_path" an _not_ with the current
search path at the time pof creation.
Ho to reproduce the bug:
/*
event=# show search_path;
search_path
-------------
public
*/
CREATE SCHEMA foo;
CREATE TABLE foo.adr
( adr_id integer primary key,
note text);
INSERT INTO foo.adr VALUES (1, 'note from table foo.adr');
CREATE FUNCTION f_test()
RETURNS text AS
'SELECT note FROM adr where adr_id = 1'
LANGUAGE 'sql'
SET search_path=foo;
-- ERROR: relation "adr" does not exist
-- LINE 3: 'SELECT note FROM adr where adr_id = 1'
-- Function body is falsely checked with "search_path=
public" (current search_path) instead of "search_path=foo" AS it
should be!
-- If I disable check_function_bodies before creation, creation works
and the function call works and returns the value of foo.adr
correctly.
SET check_function_bodies=false;
Regards
Erwin Brandstetter
