Merge multiple result tables and perform final query on result

Question

I have a function returning table, which accumulates output of multiple calls to another function returning table. I would like to perform final query on built table before returning result. Currently I implemented this as two functions, one accumulating and one performing final query, which is ugly:

CREATE OR REPLACE FUNCTION func_accu(LOCATION_ID INTEGER, SCHEMA_CUSTOMER TEXT)
  RETURNS TABLE("networkid" integer, "count" bigint) AS $$
DECLARE
GATEWAY_ID integer;
BEGIN
    FOR GATEWAY_ID IN
        execute format(
            'SELECT id FROM %1$I.gateway WHERE location_id=%2$L'
           , SCHEMA_CUSTOMER, LOCATION_ID)
    LOOP
        RETURN QUERY execute format(
            'SELECT * FROM get_available_networks_gw(%1$L, %2$L)'
           , GATEWAY_ID, SCHEMA_CUSTOMER);
    END LOOP;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION func_query(LOCATION_ID INTEGER, SCHEMA_CUSTOMER TEXT)
  RETURNS TABLE("networkid" integer, "count" bigint) AS $$
DECLARE
BEGIN
    RETURN QUERY execute format('
       SELECT networkid, max(count) FROM func_accu(%2$L, %1$L) GROUP BY networkid;'
     , SCHEMA_CUSTOMER, LOCATION_ID);
END;
$$ LANGUAGE plpgsql;

How can this be done in single function, elegantly?

Remember to always provide your version of Postgres and the necessary context - at least the exact result type of `get_available_networks_gw(..)` — Erwin Brandstetter, Oct 12 '15 at 02:18

score 1 · Accepted Answer · edited May 23 '17 at 11:54

Both functions simplified and merged, also supplying value parameters in the USING clause:

CREATE OR REPLACE FUNCTION pg_temp.func_accu(_location_id integer, schema_customer text)
  RETURNS TABLE(networkid integer, count bigint) AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT f.networkid, max(f.ct)
      FROM   %I.gateway g
           , get_available_networks_gw(g.id, $1) f(networkid, ct)
      WHERE  g.location_id = $2
      GROUP  BY 1'
    , _schema_customer)
   USING  _schema_customer, _location_id;
END
$func$ LANGUAGE plpgsql;

Call:

SELECT * FROM func_accu(123, 'my_schema');

Dynamically access column value in record

I am using alias names for the columns returned by the function (f(networkid, ct)) to be sure because you did not disclose the return type of get_available_networks_gw(). You can use the column names of the return type directly.

The comma (,) in the FROM clause is short syntax for CROSS JOIN LATERAL .... Requires Postgres 9.3 or later.

What is the difference between LATERAL and a subquery in PostgreSQL?

Or you could run this query instead of the function:

SELECT f.networkid, max(f.ct)
FROM   myschema.gateway g, get_available_networks_gw(g.id, 'my_schema') f(networkid, ct)
WHERE  g.location_id = $2
GROUP  BY 1;

Thank you, your solution is perfect and well explained! – Miha Oct 12 '15 at 08:54 — Miha, Oct 12 '15 at 08:54

Merge multiple result tables and perform final query on result

1 Answers1