如何在 PostgreSQL 表函数中高效实现参数化 ORDER BY 和 LIMIT 子句？

表函数中的 PostgreSQL 参数化排序依据/限制

利用 PostgreSQL 中的表函数可以实现高效的数据操作和检索。然而，在处理大型数据集时，应用排序和分页来有效处理结果变得至关重要。

考虑以下场景：我们有一个表函数 getStuff，它对名为的表执行简单的 SELECT 查询stuff:

CREATE OR REPLACE FUNCTION getStuff(param character varying)
  RETURNS SETOF stuff AS
$BODY$
    select *
    from stuff
    where col = 
$BODY$
  LANGUAGE sql;

最初，我们按如下方式调用该函数：

select * from getStuff('hello');

但是，为了优化性能，我们需要应用 ORDER BY 子句和 LIMIT 子句。不幸的是，执行这样的查询：

select * from getStuff('hello') order by col2 limit 100;

将从 stuff 表中检索所有行，然后对它们进行排序和限制，导致处理效率低下。

此外，PostgreSQL 不提供将 ORDER BY 参数传递给 SQL 语言函数的固有方法，因为只能传输值。在 plpgsql 函数中构造查询并通过 EXECUTE 执行它是一种替代方法，但这不是最优雅的解决方案。

解决方案：

幸运的是，plpgsql 函数提供了一种有效的解决方案这个场景。我们可以修改 getStuff 函数以接受 ORDER BY 和 LIMIT 的附加参数：

CREATE OR REPLACE FUNCTION getStuff(param character varying, orderby character varying, _limit int)
  RETURNS SETOF stuff AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
      SELECT *
      FROM   stuff
      WHERE  col = 
      ORDER  BY ' || quote_ident(_orderby) || ' ASC
      LIMIT  '
   USING _param, _limit;
END
$func$  LANGUAGE plpgsql;

现在，我们可以使用所需的参数调用该函数：

SELECT * FROM getStuff('hello', 'col2', 100);

RETURN QUERY EXECUTE语句有效地检索函数内查询的结果。

以上是如何在 PostgreSQL 表函数中高效实现参数化 ORDER BY 和 LIMIT 子句？的详细内容。更多信息请关注PHP中文网其他相关文章！