如何修改 PL/pgSQL 函數以傳回動態 SQL 查詢的結果？

修改PL/pgSQL函數以傳回查詢結果

背景

最初，您建立了一個函數，該函數輸出格式良好的PostgreSQL SELECT 查詢字串。現在，您希望直接對資料庫執行產生的SELECT語句並檢索其結果。

方法：動態SQL與回傳型別

動態SQL

要執行動態SQL，我們使用EXECUTE指令。由於您沒有使用遊標，因此您可以使用RETURN QUERY EXECUTE語法。

回傳類型

挑戰在於傳回未定義類型的記錄，因為函數需要宣告傳回型別。我們將根據資料的具體特徵考慮各種方法。

使用固定回傳類型的簡單解決方案

假設使用時間戳記、文字和文字列的固定回傳類型，我們可以將函數定義如下：

CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, col2 text, col3 text)
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora, %s
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _id;
END
$$ LANGUAGE plpgsql;

同類型的可變列數

如果您有可變數量的列，且所有列的類型相同（例如，雙精確度），我們可以使用ARRAY類型巢狀值：

CREATE FUNCTION data_of(_id integer)
RETURNS TABLE (datahora timestamp, names text[], values float8[])
AS $$
DECLARE
   _sensors text := 'col1, col2, col3';
   _type text := 'foo';
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT datahora
           , string_to_array(, ',')  -- AS names
           , ARRAY[%s]            -- AS values
       FROM   %s
       WHERE  id = 
       ORDER  BY datahora',
      _sensors,
      _type
   )
   USING _sensors, _id;
END
$$ LANGUAGE plpgsql;

各種完整的表格類型

要傳回表格的全部列，我們可以使用多型型別：

CREATE FUNCTION data_of(_tbl_type anyelement, _id int)
RETURNS SETOF anyelement
AS $$
BEGIN
   RETURN QUERY EXECUTE format(
      'SELECT *
       FROM   %I  -- pg_typeof returns regtype, quoted automatically
       WHERE  id = 
       ORDER  BY datahora',
      pg_typeof(_tbl_type)
   )
   USING _id;
END
$$ LANGUAGE plpgsql;

注意：第二個例子中，string_to_array 函數需要一個分隔符，這裡加入了 , 作為分隔符。 第三個範例使用了 %I 格式化標識符，以更安全的方式處理表名，以防止SQL注入。 選擇哪種方法取決於您的特定需求和資料結構。 如果可能，提供更多關於您的表結構和所需結果的信息，可以幫助我提供更精確和有效的解決方案。

以上是如何修改 PL/pgSQL 函數以傳回動態 SQL 查詢的結果？的詳細內容。更多資訊請關注PHP中文網其他相關文章！