如何在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中文網其他相關文章！