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

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

创建了一个名为 getStuff 的自定义 SQL 函数来执行基本的 SELECT 语句。当使用 select * from getStuff('hello') 形式的函数时，它会根据提供的参数值从 stuff 表中获取所有行。

但是，问题出现了：我们如何实现排序并限制此函数以提高效率并仅检索所需的行？直接在 getStuff 函数中使用 order by 和 limit 子句效率很低，因为它涉及在应用过滤器之前获取所有行。

推荐的解决方案是利用 PL/pgSQL（PostgreSQL 中的一种过程语言）来创建功能。 PL/pgSQL 支持动态 SQL 查询的构建和执行，提供处理参数值的灵活性。

PL/pgSQL 中的示例函数：

CREATE OR REPLACE FUNCTION get_stuff(_param text, _orderby text, _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;

此函数采用三个参数： _param （筛选依据的值）、_orderby（排序依据的列）和 _limit（要排序的最大行数） return)。

调用函数：

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

通过使用RETURN QUERY EXECUTE，批量返回构造的SQL查询的结果。 quote_ident 通过转义标识符来防止 SQL 注入。参数通过 USING 传递，以避免 SQL 注入和潜在的转换/引用问题。

对于更复杂的查询，请考虑使用 format() 函数。

尽管 SQL 函数可能适合简单操作，PL/pgSQL 在处理表函数中的参数化 ORDER BY 和 LIMIT 子句时提供了更大的灵活性和优化能力。

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