如何重構 PL/pgSQL 函數以從具有可變列號和類型的動態 SELECT 查詢傳回結果？

重建PL/pgSQL函數以傳回各種SELECT查詢的輸出

簡介

您有一個PL/pgSQL函數，它產生一個SELECT查詢作為文字字串。現在，您希望進一步增強函數以執行產生的查詢並傳回實際結果，類似於獨立查詢的行為。

可變回傳類型

挑戰在於傳回資料的結構和類型可能會因被查詢的基礎表而異。一個簡單的解決方案是定義一個具有通用列名的固定返回類型，並將所有值強制轉換為文本，如下所示的修改後的函數：

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   _sensors text := 'col1::text, col2::text';
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = 
      ORDER  BY datahora'
   USING  _id;
END
$func$;</code>

處理可變列數

如果傳回的列數變化，但類型保持不變（例如，所有都是float8），則可以使用陣列巢狀值：

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, names text[], values float8[])
  LANGUAGE plpgsql AS
$func$
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
$func$;</code>

動態回傳任何表格型別

為了處理具有不同結構的各種完整表類型，您可以利用多態類型：

<code class="language-sql">CREATE OR REPLACE FUNCTION data_of(_tbl_type anyelement, _id int)
  RETURNS SETOF anyelement
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format('
      SELECT *
      FROM   %s  -- pg_typeof returns regtype, quoted automatically
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

此函數要求您傳遞一個強制轉換為目標表類型的NULL值（例如，NULL::pcdmet）。然後它會自動確定適當的行類型，並在與SELECT * FROM data_of()一起使用時傳回各個列。

以上是如何重構 PL/pgSQL 函數以從具有可變列號和類型的動態 SELECT 查詢傳回結果？的詳細內容。更多資訊請關注PHP中文網其他相關文章！