如何重構 PL/pgSQL 函數來處理動態 SQL 回傳類型？

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

動態SQL與回傳型別

目前，您的函數傳回一個表示產生的SELECT語句的文字字串。為了自動執行並傳回結果，您可以使用動態SQL。但是，困難在於未定義的回傳類型。函數在RETURNS子句中需要定義回傳類型。

固定回傳類型解決方案

假設返回類型固定，包含名為datahora的時間戳列和兩個名稱和類型不同的附加列，您可以實現以下內容：

<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>

列數可變，但型別相同

如果傳回類型具有可變數量的列，但所有列的類型相同（例如，雙精確度），則使用陣列類型：

<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()  -- 作为名称
           , ARRAY[%s]            -- 作为值
      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 返回 regtype，自动引用
      WHERE  id = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$;</code>

使用方法：

<code class="language-sql">SELECT * FROM data_of(NULL::pcdmet, 17);</code>

以上是如何重構 PL/pgSQL 函數來處理動態 SQL 回傳類型？的詳細內容。更多資訊請關注PHP中文網其他相關文章！