Preserve PostgreSQL schema identifier in dependent function calls

Question

I have a stored procedure that RETURNS record:

CREATE OR REPLACE FUNCTION r_rpt_prov_summary() 
RETURNS record 
AS $$ 
  DECLARE
    _fields             record;
  BEGIN
    SELECT INTO _fields
      0::integer AS customers,
      0::integer AS customers_active;

    SELECT INTO _fields.customers count(*) FROM customer;
    SELECT INTO _fields.customers_active count(*) FROM customer WHERE active = 't';

    RETURN _fields;
  END
$$ LANGUAGE 'plpgsql';

However, in order to query it, I would have to explicitly enumerate the returned columns and types:

SELECT * FROM r_rpt_prov_summary() AS (a integer, b integer);

In order to make this palatable to an MVC framework, which by its nature wants to query tables, I wrap it in an SQL function that RETURNS TABLE:

CREATE OR REPLACE FUNCTION rpt_prov_summary() 
RETURNS TABLE (
        customers               integer,
        customers_active        integer
) AS $$
  SELECT * FROM r_rpt_prov_summary() AS (customers integer, customers_active integer);
$$ LANGUAGE 'sql';

This works very well as long as both functions reside in the same schema or search_path space. However, in this case, they live in a nonstandard schema of their own, so I have to query the outer function as myschema.rpt_prov_summary(), i.e.

SELECT * FROM myschema.rpt_prov_summary();

This doesn't work if my schema and search path are set to public:

test=> SELECT * FROM myschema.rpt_prov_summary();
ERROR:  function r_rpt_prov_summary() does not exist
LINE 2:   SELECT * FROM r_rpt_prov_summary() AS (customers integer, ...
                        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Naturally, your thoughts would turn to calling SET SCHEMA 'myschema' or SET search_path TO myschema prior to query execution. So did mine. The problem is it's just not going to work for the environment from which I'm calling. The MVC framework is using prepared statements to construct the query, and PostgreSQL frowns upon something like:

SET search_path TO myschema;SELECT * FROM rpt_prov_summary();

Which is to say:

< 2016-03-14 20:50:46.410 EDT >ERROR: cannot insert multiple commands into a prepared statement

So, that's not going to work. Providing the schema as an argument to the outer function isn't going to work, either; I just don't have that flexibility within the constraints of the MVC framework, which wants to be querying plain old tables or things that act like tables.

current_schema identifies the current schema of the client session, so that's not going to help. Already tried this:

CREATE OR REPLACE FUNCTION rpt_prov_summary() 
RETURNS TABLE (
    customers       integer,
    customers_active    integer
) AS $$
  BEGIN
    RAISE NOTICE 'Current schema: %', current_schema;
    RETURN QUERY EXECUTE 'SELECT * FROM ' || current_schema || '.r_rpt_prov_summary() AS (customers integer, customers_active integer)';
  END
$$ LANGUAGE 'plpgsql';

No dice - Current schema: public, as expected.

Is there any way to somehow capture the schema space of the outer call and propagate that into the encapsulated query?

Answer 1

1.

I have a stored procedure ...

No, you don't. You have a function, which is almost but not quite the same. Postgres doesn't currently support stored procedures.

2.

Radically simplify. Instead of your two nested functions, use one simple SQL function with OUT parameters:

CREATE OR REPLACE FUNCTION myschema.r_rpt_prov_summary(  -- schema-qualify!
          OUT _customers integer
        , OUT _customers_active integer) AS
$func$
   SELECT count(*)::int
        , count(*) FILTER (WHERE active)::int  -- requires Postgres 9.4+
   FROM   myschema.customer;                   -- schema-qualify!
$func$ LANGUAGE sql;                           -- don't quote the language name

Call:

SELECT * FROM myschema.rpt_prov_summary();

Works independently of your current search_path setting.

Note a subtle difference between RETURNS TABLE() and RETURNS record (my version is also RETURNS record, but I didn't specify explicitly after declaring OUT parameters!): The later always returns exactly 1 row (and Postgres knows that and can rely on it), while the former can return 0 - n rows.

3.

You could set the search_path in many different ways, even as local setting to the function itself - if necessary:

• How does the search_path influence identifier resolution and the "current schema"

To answer your actual question:

Is there any way to somehow capture the schema space of the outer call and propagate that into the encapsulated query?

CREATE OR REPLACE FUNCTION myschema.r_rpt_prov_summary(OUT _customers integer
                                                     , OUT _customers_active integer) AS 
$func$ 
BEGIN
   SELECT INTO _customers, _customers_active
          count(*)::int, count(*) FILTER (WHERE active)::int  
   FROM   customer;  -- no schema-qualification
END
$func$  LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION myschema.rpt_prov_summary() 
  RETURNS TABLE (customers int, customers_active int) AS
$func$
   SELECT * FROM myschema.r_rpt_prov_summary();
$func$ LANGUAGE sql SET search_path = myschema;  -- set the search_path here

The search_path is propagated to the nested function - just what you were looking for.

Or just schema-qualify identifiers everywhere (including function names) to be unambiguous.