如何重构 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中文网其他相关文章！