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?

And yes, I've thought of fishing the relation name out of current_query(), but eugh! — Alex Balashov
– Alex Balashov, Commented Mar 15, 2016 at 1:11
In your function rpt_prov_summary() why didn't you specify the schema explicitly? Instead of SELECT * FROM r_rpt_prov_summary() AS (...) try SELECT * FROM myschema.r_rpt_prov_summary() AS (...) — pumbo
– pumbo, Commented Mar 15, 2016 at 3:52
@pumbo Because it's dynamic, not static. I don't know which schema I need to access in advance. That token is part of the query constructed by the MVC framework. — Alex Balashov
– Alex Balashov, Commented Mar 16, 2016 at 21:45

Community · Accepted Answer · 2017-05-23 12:07:50Z

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.

Thank you for your insights and clarifications; you are definitely very knowledgeable. What I got out of your response was that I can use OUT parameters to make this entire issue go away by eliminating the need for an encapsulating function, so that's what I did. Also, thanks for cluing me into FILTER!
@AlexBalashov: Details for aggregate FILTER: stackoverflow.com/a/27141193/939860

Collectives™ on Stack Overflow

Preserve PostgreSQL schema identifier in dependent function calls

1 Answer 1

1.

2.

3.

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1.

2.

3.

2 Comments

Linked

Related