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