sql - 如何将一组行从一个函数传递到另一个函数？

Question

概述

我正在使用 PostgreSQL 9.1.14，并且我试图将一个函数的结果传递给另一个函数。一般的想法(细节，用一个最小的例子，如下)是我们可以写:

select * from (select * from foo ...) 

我们可以在一个函数中抽象出子选择并从中选择:

create function foos() 
returns setof foo
language sql as $$
  select * from foo ...
$$;

select * from foos()

有没有办法进一步抽象一层，以便能够做这样的事情(我知道函数实际上不能有 setof 类型的参数):

create function more_foos( some_foos setof foo )
language sql as $$
  select * from some_foos ...  -- or unnest(some_foos), or ???
$$:

select * from more_foos(foos())

最小示例和尝试的解决方法

我正在使用 PostgreSQL 9.1.14。这是一个最小的例子:

-- 1. create a table x with three rows                                                                                                                                                            
drop table if exists x cascade;
create table if not exists x (id int, name text);
insert into x values (1,'a'), (2,'b'), (3,'c');

-- 2. xs() is a function with type `setof x`
create or replace function xs()
returns setof x
language sql as $$
  select * from x
$$;

-- 3. xxs() should return the context of x, too
--    Ideally the argument would be a `setof x`,
--    but that's not allowed (see below).
create or replace function xxs(x[])  
returns setof x
language sql as $$
  select x.* from x
  join unnest($1) y
       on x.id = y.id
$$;

当我加载这段代码时，我得到了表定义的预期输出，我可以调用并从中选择 xs()正如我所料。但是当我尝试传递 xs() 的结果时至 xxs() ，我收到“函数 xxs(x) 不存在”的错误消息:

db=> \i test.sql 
DROP TABLE
CREATE TABLE
INSERT 0 3
CREATE FUNCTION
CREATE FUNCTION

db=> select * from xs();
  1 | a
  2 | b
  3 | c

db=> select * from xxs(xs());
ERROR:  function xxs(x) does not exist
LINE 1: select * from xxs(xs());
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

我对“函数 xxs(x) 不存在”有点困惑；因为返回类型是 xs()是 setof x ，我原以为它的返回类型是 setof x (或者可能 x[])，而不是 x .根据有关类型的投诉，我可以使用以下任一方法，但是在使用任一定义时，我都可以 select xxs(xs()); ，我不能 select * from xxs(xs()); .

create or replace function xxs( x )
returns setof x
language sql as $$
  select x.* from x
  join unnest(array[$1]) y    -- unnest(array[...]) seems pretty bad
       on x.id = y.id
$$;

create or replace function xxs( x )
returns setof x
language sql as $$
  select * from x
         where x.id in ($1.id)
$$;

db=> select xxs(xs());
 (1,a)
 (2,b)
 (3,c)

db=> select * from xxs(xs());
ERROR:  set-valued function called in context that cannot accept a set

概括

将集合返回函数的结果传递给另一个函数的正确方法是什么？
(我注意到 create function ... xxs( setof x ) ... 导致错误: ERROR: functions cannot accept set arguments ，所以答案不会从字面上传递一个集合从一个函数到另一个函数的行数。)

Answer 1

表函数

我以高速、复杂的数据库迁移为生，使用 SQL 作为客户端和服务器语言(不使用其他语言)，全部运行在服务器端，代码很少从数据库引擎中出现。 表函数在我的工作中发挥了巨大的作用 .我不使用“游标”，因为它们太慢而无法满足我的性能要求，而且我所做的一切都是面向结果的。表函数在完全消除游标的使用、实现非常高的速度方面对我有很大帮助，并且对减少代码量和提高简单性做出了巨大贡献。

简而言之，您使用的是 查询 引用两个(或更多)表函数以将数据从一个表函数传递到下一个。 调用表函数的选择查询结果集充当将数据从一个表函数传递到下一个表函数的管道。 在我工作的 DB2 平台/版本上，根据对 9.1 Postgres 手册的快速浏览，在那里也是如此，您只能将单行列值作为输入传递给任何表函数调用，正如你所发现的。 但是，由于表函数调用发生在查询结果集处理的中间，因此您可以获得将整个结果集传递给每个表函数调用的相同效果，尽管在数据库引擎管道中，数据仅传递一行一次给每个表函数。

表函数接受一行输入列，并将单个结果集返回到调用该函数的调用查询(即选择)中。 从表函数传回的结果集列成为调用查询结果集的一部分，因此可用作下一个表函数 的输入。 ，稍后在同一查询中引用，通常作为后续连接。第一个表函数的结果列作为输入(一次一行)提供给第二个表函数，后者将其结果集列返回到调用查询的结果集中。第一个和第二个表函数结果集列现在都是调用查询结果集的一部分，现在可用作第三个表函数的输入(一次一行)。 每个表函数调用通过它返回的列扩展调用查询的结果集。 这可以持续下去，直到您开始达到结果集宽度的限制，这可能因一个数据库引擎而异。

考虑这个示例(当我在 DB2 上工作时，它可能与 Postgres 的语法要求或功能不匹配)。这是我使用表函数的众多设计模式之一，也是比较简单的一种，我认为它非常具有说明性，而且我预计它会具有广泛的吸引力 如果 表函数被大量使用(据我所知它们不是，但我认为它们应该得到更多的关注)。

在此示例中，使用的表函数为:VALIDATE_TODAYS_ORDER_BATCH、POST_TODAYS_ORDER_BATCH 和 DATA_WAREHOUSE_TODAYS_ORDER_BATCH。在我使用的 DB2 版本上，您将表函数包装在“TABLE(在此处放置表函数调用和参数)”中，但是根据快速查看 Postgres 手册，您似乎省略了“TABLE()”包装器。

create table TODAYS_ORDER_PROCESSING_EXCEPTIONS as (

select      TODAYS_ORDER_BATCH.*
           ,VALIDATION_RESULT.ROW_VALID
           ,POST_RESULT.ROW_POSTED
           ,WAREHOUSE_RESULT.ROW_WAREHOUSED

from        TODAYS_ORDER_BATCH

cross join  VALIDATE_TODAYS_ORDER_BATCH ( ORDER_NUMBER, [either pass the remainder of the order columns or fetch them in the function]  ) 
              as VALIDATION_RESULT ( ROW_VALID )  --example: 1/0 true/false Boolean returned

left join   POST_TODAYS_ORDER_BATCH ( ORDER_NUMBER, [either pass the remainder of the order columns or fetch them in the function] )
              as POST_RESULT ( ROW_POSTED )  --example: 1/0 true/false Boolean returned
      on    ROW_VALIDATED = '1'

left join   DATA_WAREHOUSE_TODAYS_ORDER_BATCH ( ORDER_NUMBER, [either pass the remainder of the order columns or fetch them in the function] )
              as WAREHOUSE_RESULT ( ROW_WAREHOUSED )  --example: 1/0 true/false Boolean returned
      on    ROW_POSTED = '1'

where       coalesce( ROW_VALID,      '0' ) = '0'   --Capture only exceptions and unprocessed work.  
      or    coalesce( ROW_POSTED,     '0' ) = '0'   --Or, you can flip the logic to capture only successful rows.
      or    coalesce( ROW_WAREHOUSED, '0' ) = '0'

) with data

如果表 TODAYS_ORDER_BATCH 包含 1,000,000 行，则
VALIDATE_TODAYS_ORDER_BATCH 将被调用 1,000,000 次，一次为
每一行。

如果 VALIDATE_TODAYS_ORDER_BATCH 中有 900,000 行通过验证，则 POST_TODAYS_ORDER_BATCH 将被调用 900,000 次。

如果只有 850,000 行成功发布，那么 VALIDATE_TODAYS_ORDER_BATCH 需要关闭一些漏洞 LOL，并且 DATA_WAREHOUSE_TODAYS_ORDER_BATCH 将被调用 850,000 次。

如果 850,000 行成功进入数据仓库(即没有产生额外的异常)，那么表 TODAYS_ORDER_PROCESSING_EXCEPTIONS 将填充 1,000,000 - 850,000 = 150,000 异常行。

此示例中的表函数调用仅返回单个列，但它们可能返回多个列。例如，验证订单行的表函数可以返回订单验证失败的原因。

在这种设计中，几乎消除了 HLL 和数据库之间的所有对话，因为 HLL 请求者要求数据库在一个请求中处理整个批次。这导致对数据库的数百万个 SQL 请求减少，大量删除数百万个 HLL 过程或方法调用，因此提供了巨大的运行时改进。相比之下，通常一次处理一行的遗留代码通常会发送 1,000,000 个 fetch SQL 请求，TODAYS_ORDER_BATCH 中的每行 1 个，加上至少 1,000,000 个用于验证目的的 HLL 和/或 SQL 请求，加上至少 1,000,000 个 HLL 和/或用于发布目的的 SQL 请求，以及用于将订单发送到数据仓库的 1,000,000 个 HLL 和/或 SQL 请求。当然，使用这种表函数设计，表函数内部的 SQL 请求被发送到数据库，但是当数据库向自身发出请求时(即从表函数内部)，SQL 请求的服务速度要快得多(特别是与HLL 请求者从远程系统进行单行处理的遗留场景，最坏的情况是通过 WAN - OMG 请不要这样做)。

如果使用表函数“获取结果集”然后将该结果集连接到其他表，则很容易遇到性能问题。在这种情况下，SQL 优化器无法预测表函数将返回哪些行集，因此它无法优化与后续表的连接。出于这个原因，我很少使用它们来获取结果集，除非我知道结果集将是非常少量的行，因此不会导致性能问题，或者我不需要连接到后续表。

在我看来，表函数未被充分利用的一个原因是，它们通常被认为只是一种获取结果集的工具，通常性能很差，因此它们被认为是一种“糟糕”的工具。

表函数对于将更多功能推送到服务器、消除数据库服务器和远程系统上的程序之间的大部分干扰，甚至消除数据库服务器和同一服务器上的外部程序之间的干扰非常有用。即使是同一服务器上的程序之间的交流也比许多人意识到的要多，而且很多都是不必要的。表函数的核心在于使用它们在结果集处理中执行操作。

有使用基于上述模式的表函数的更高级设计模式，您可以在其中进一步最大化结果集处理，但这篇文章已经为大多数人吸收了很多。