postgresql - 合并多个结果表并对结果进行最终查询

Question

我有一个函数返回表，它累积了对另一个函数返回表的多次调用的输出。我想在返回结果之前对构建的表执行最终查询。目前，我将其实现为两个函数，一个累积，一个执行最终查询，这很丑陋:

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;

这如何在单一功能中优雅地完成？

Answer 1

两个函数都经过简化和合并，还在 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;

调用:

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

相关:

• Dynamically access column value in record

我正在为函数 ( f(networkid, ct) ) 返回的列使用别名来确定，因为您没有透露 get_available_networks_gw() 的返回类型.您可以直接使用返回类型的列名。

, 中的逗号 ( FROM)子句是 CROSS JOIN LATERAL ... 的简短语法.需要 Postgres 9.3 或更高版本。

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

或者您可以运行此查询而不是函数:

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;