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