使用Postgres获取每个查询的singleton值

Question

由于Postgres似乎不具有每个UDF多个结果集的功能（SQL Server具有）。返回查询范围内单例值的最佳方式是什么？例如，当对FTS搜索的结果进行分页时，如果我们能够获得与查询匹配的结果数量（而不必使用分页对所有页面进行迭代），这将简化逻辑。

方法一：将post_count粘贴到SELECT列表中。缺点：重复

方法二：编写一个单独的UDF来获得post_count cons:多个UDF调用（我使用这种方法，它将显示第一个页面的延迟增加了一倍）。

方法三：对结果集使用数组，并将post_count作为结果集的兄弟项放在最高层cons:慢得多-也许这是由于array_agg()函数的原因（是的，我尝试过这种方法）。

那么，是否有更务实的解决方案来解决这个问题，如果没有，开发过程中是否有任何东西可以解决这个问题？

Answer 1

我自己多次遇到这个问题，但没有找到一个解决方案。

我最新的方法是将返回的SET的第一行定义为元数据你的方法列表中还没有这个。应用程序使用第一行进行记账。实际数据从第二行开始。

显而易见的弱点是：无论你必须在元数据中压缩什么行定义，你都必须将就。不过，一个简单的总数可以适用于任何数字或字符串类型
当然，您必须对应用程序中的第一行进行特殊处理。

这个简单的示例返回定义为的表foo中的行

CREATE TABLE foo (
  foo_id serial PRIMARY KEY
 ,foo    text
 );

页面大小为20行，默认情况下在功能标题中预设：

CREATE OR REPLACE FUNCTION f_paginate(_max_id int, _limit int = 20
                                                 , _offset int = 0)
  RETURNS TABLE(foo_id int, foo text) AS
$BODY$
BEGIN
SELECT INTO foo_id  count(*)::int
FROM   foo f
WHERE  f.foo_id < _max_id; -- get count
RETURN NEXT;               -- use first row for meta-data
RETURN QUERY               -- actual data starts with second row
SELECT f.foo_id, f.foo
FROM   foo f
WHERE  f.foo_id < _max_id
LIMIT  _limit
OFFSET _offset;
END;
$BODY$
  LANGUAGE plpgsql;

呼叫：

SELECT * FROM f_paginate(100);

退货：

foo_id | foo
-------+----
86     | <NULL>    <-- first row = meta-data
1      | bar       <-- actual data
2      | baz
... 18 more ...

显然，这种方法可以通过更高的_limit（页面大小）来节省一些带宽。只有几排，几乎不值得花这么多钱。

另一种方法是"方法一"-冗余添加一列：

CREATE OR REPLACE FUNCTION f_paginate2(_max_id int, _limit int = 20
                                                  , _offset int = 0)
  RETURNS TABLE(foo_id int, foo text, ct bigint) AS
$BODY$
BEGIN
RETURN QUERY
SELECT f.foo_id, f.foo, count(*) OVER ()
FROM   foo f
WHERE  f.foo_id < _max_id
LIMIT  _limit
OFFSET _offset;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

呼叫：

SELECT * FROM f_paginate2(100);

退货：

foo_id | foo | ct
-------+-----+----
1      | bar | 86
2      | baz | 86
... 18 more ...

在这个简单的案例中，性能非常相似。第一个查询稍微快一点，但可能只是因为count(*) OVER ()减慢了第二个查询的速度。仅使用count(*)单独运行会更快。