PL/pgSQL function name resolution with nested functions

Question

I have two similar schemas in a single database with the same function names. Each schema is owned by a role that matches the schema name.

I have issues about function name resolution with nested functions. I was expecting that the outer function would call inner functions within the same schema, but it does not! The name is resolved dynamically based on the search_path at run time which make some sens, but not as I would.

Here is a test case. Let say for example that the schemas and roles are named test and prod as follow.

Test schema:

CREATE ROLE test NOLOGIN;
CREATE SCHEMA test AUTHORIZATION test;

CREATE OR REPLACE FUNCTION test.inner_func() RETURNS TEXT
AS $BODY$
BEGIN
   RETURN 'test function';
END
$BODY$ LANGUAGE 'plpgsql';
ALTER FUNCTION test.inner_func() OWNER TO test;

CREATE OR REPLACE FUNCTION test.outer_func() RETURNS SETOF TEXT
AS $BODY$
BEGIN
   RETURN QUERY SELECT inner_func();
END
$BODY$ LANGUAGE 'plpgsql';
ALTER FUNCTION test.outer_func() OWNER TO test;

Prod schema:

CREATE ROLE prod NOLOGIN;
CREATE SCHEMA prod AUTHORIZATION prod;

CREATE OR REPLACE FUNCTION prod.inner_func() RETURNS TEXT
AS $BODY$
BEGIN
   RETURN 'prod function';
END
$BODY$ LANGUAGE 'plpgsql';
ALTER FUNCTION prod.inner_func() OWNER TO prod;

CREATE OR REPLACE FUNCTION prod.outer_func() RETURNS SETOF TEXT
AS $BODY$
BEGIN
   RETURN QUERY SELECT inner_func();
END
$BODY$ LANGUAGE 'plpgsql';
ALTER FUNCTION prod.outer_func() OWNER TO prod;

Test cases:

SET search_path=test,public;    
SELECT outer_func();
> test function

SELECT prod.outer_func();
> test function <<<---- was expecting prod function 

SET search_path=prod,public;
SELECT prod.outer_func();
> prod function

The test shows that function names are resolved dynamically based on the search_path at run time. Is there a way to bind inner function within the scope of a schema?

I can get such a behavior by using SECURITY DEFINER functions with dynamic SQL and CURRENT_USER, but I am looking for something more straightforward.

You can set the required search path for as an attribute of the function (using `SET` in the function definition). Btw: the language name is an identifier, do not put it into single quotes. That is deprecated syntax that is going to be removed in the future — , May 01 '15 at 15:05
@ a_horse_with_no_name Nice! work so well. Please use the answer button instead of comment so I can assign points and set the question as answered. — lessj, May 01 '15 at 16:34
@a_horse_with_no_name is correct. But, I'm not sure why you just don't set the search_path after connection. What you are doing here sounds a bit like the pretty common multi-tenant pattern where you create series of identical schema based on a customer name, and then when you connect, you set the search path to the current customer. Running the system as "test" sets the search_path after connecting, etc. — David S, May 01 '15 at 17:23
@Davis S. Let say you have a team of 7 to 8 tier1 and tier3 users that have their own account to manage several customer schemas. Tier3 DBAs won't have problem to set the search path, but may forget and screw up things easily. However, you can certainly imagine the damage that Tier1 support, that are used to work with Oracle, can do — lessj, May 01 '15 at 21:50

score 1 · Answer 1 · edited May 23 '17 at 12:09

The clean solution is to either schema-qualify the function:

CREATE OR REPLACE FUNCTION test.outer_func()
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY SELECT test.inner_func();
END
$func$  LANGUAGE plpgsql;  -- no quotes!

Or you explicitly set the search_path per function. You can set configuration parameters this way:

CREATE OR REPLACE FUNCTION test.outer_func()
  RETURNS SETOF text AS
$func$
BEGIN
   RETURN QUERY SELECT inner_func();
END
$func$  LANGUAGE plpgsql SET search_path = test, pg_temp;

Customize the search_path to your needs, possibly add public to the list. I put pg_temp at the end, so objects in the temporary schema cannot hide persisted objects. (But that's not applicable for functions.) Similar to what's explained in the manual for SECURITY DEFINER functions.

I would not advise to rely on the user setting the proper search_path. That would only make sense for "public" functions, it wouldn't be consistent with your design. Why create separate functions and then still have to rely on user settings after all? You could have a single function in the public schema to begin with, but I would not got that route in any case. Very confusing and error prone.

Also, PL/pgSQL executes statements like prepared statements internally. every time you change the search_path, all "prepared" statements from plpgsql functions have to be de-allocated, which is not helping to optimize performance.

Actually, your test case in the question only works if you set the search_path first:

SET search_path=test,public;

Else you get an error when trying to create

CREATE OR REPLACE FUNCTION test.outer_func() RETURNS SETOF TEXT
AS $BODY$
BEGIN
   RETURN QUERY SELECT inner_func();
...

ERROR: function inner_func() does not exist

Syntax checks are run against the current search_path at creation time - unless you provide the search_path as suggested. That was fixed 2010 after I reported a bug.

Details for search_path:

And don't quote the language name. It's an identifier.

PL/pgSQL function name resolution with nested functions

1 Answers1