重构PL / pgSQL函数以返回各种SELECT查询的输出

Question

我编写了一个函数，该函数输出SELECT格式正确的PostgreSQL查询。现在，我不再想要输出文本，而是实际上SELECT对数据库运行生成的语句并返回结果-
就像查询本身一样。

到目前为止，我有：

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS text AS
$BODY$
DECLARE
   sensors varchar(100);   -- holds list of column names
   type    varchar(100);   -- holds name of table
   result  text;           -- holds SQL query
       -- declare more variables

BEGIN
      -- do some crazy stuff

      result := 'SELECT\r\nDatahora,' || sensors ||
      '\r\n\r\nFROM\r\n' || type ||
      '\r\n\r\nWHERE\r\id=' || $1 ||'\r\n\r\nORDER BY Datahora;';

      RETURN result;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

sensors保留表的列名列表type。这些在函数过程中声明并填充。最终，它们具有以下值：

• sensors：'column1, column2, column3'
  除了Datahora（timestamp）外，所有列均为类型double precision。
  

• type：'myTable'
  可以是四个表之一的名称。除了common列之外，每个列都有不同的列Datahora。

基础表的定义 。

该变量sensors将保留此处显示的对应表中的 所有
列type。例如：如果type是pcdmet再sensors会'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'

变量用于构建SELECT存储在中的语句result。喜欢：

SELECT Datahora, column1, column2, column3
FROM   myTable
WHERE  id=20
ORDER  BY Datahora;

现在，我的函数将此语句返回为text。我复制粘贴并在pgAdmin中或通过psql执行它。我想自动执行此操作，自动运行查询并返回结果。我怎样才能做到这一点？

Answer 1

动态SQL和RETURN类型

（我把最好的留到了最后，请继续阅读！） 您想执行 动态SQL 。原则上，借助于plpgsql，这很简单
EXECUTE
。您 不需要 游标-
实际上，大多数情况下，没有显式游标会更好。

您遇到的问题：您想 返回尚未定义类型的记录 。函数需要使用
RETURNS 子句（或使用OUT或INOUT参数）声明返回类型。在您的情况下，您将不得不使用匿名记录，因为返回列的_number_ ， 名称 和 类型 会有所不同。喜欢：

CREATE FUNCTION data_of(integer)
  RETURNS SETOF record AS ...

但是，这不是特别有用。这样，您必须在每次调用函数时提供一个列定义列表。喜欢：

SELECT * FROM data_of(17)
AS foo (colum_name1 integer
      , colum_name2 text
      , colum_name3 real);

但是，如果您事先不知道各列，您甚至会怎么做？
你可以采取一个不太结构化文档数据类型，例如json，jsonb，hstore或xml

但是出于这个问题的目的，让我们假设您想尽可能地返回各个，正确键入和命名的列。

固定返回类型的简单解决方案

该列datahora似乎是给定的，我将假定数据类型timestamp，并且总会有另外两个具有不同名称和数据类型的列。

名称 ，我们将有利于在返回类型的通用名称的抛弃。
类型 ，我们会放弃，也和投所有text，因为 每个 数据类型可以转换为text。

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';  -- cast each col to text
   _type    text := 'foo';
BEGIN
   RETURN QUERY EXECUTE '
      SELECT datahora, ' || _sensors || '
      FROM   ' || quote_ident(_type) || '
      WHERE  id = $1
      ORDER  BY datahora'
   USING  _id;

END
$func$;

这是如何运作的？

• 变量_sensors和_type可以改为输入参数。

• 请注意该RETURNS TABLE条款。

• 注意的使用RETURN QUERY EXECUTE。这是从动态查询返回行的更优雅的方法之一。

• 我为函数参数使用一个名称，只是为了使该USING子句RETURN QUERY EXECUTE不那么混乱。$1在SQL字符串中，“参数”不是指函数参数，而是指USING子句传递的值。（$1在这个简单的示例中，两者恰好在各自的范围内。）

• 请注意以下示例值_sensors：每列都强制转换为type text。

• 这种代码非常容易受到 SQL注入的 攻击。我quote_ident()过去一直在保护自己。将几个列名集中在一起会_sensors阻止使用quote_ident()（通常是个坏主意！）。确保没有其他问题，例如通过逐个运行列名quote_ident()。一个VARIADIC参数浮现在脑海…

使用PostgreSQL 9.1+更简单

在9.1版或更高版本中，您可以format()用来进一步简化：

RETURN QUERY EXECUTE format('
   SELECT datahora, %s  -- identifier passed as unescaped string
   FROM   %I            -- assuming the name is provided by user
   WHERE  id = $1
   ORDER  BY datahora'
  ,_sensors, _type)
USING  _id;

同样，可以正确地转义各个列的名称，这将是一种干净的方法。

共享相同类型的可变列数

问题更新后，您的返回类型看起来像

• 可变 数量 的列
• 但所有相同 类型的 列double precision（别名float8）

由于我们必须定义RETURN函数的ARRAY类型，因此在这种情况下，我求助于一种类型，该类型可以容纳可变数量的值。另外，我返回了一个带有列名称的数组，因此您也可以从结果中解析名称：

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

如果您实际上是在尝试返回 表的所​​有列
（例如，链接页面上的表之一），则可以使用这种具有
多态类型的 简单，非常强大的解决方案：

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

致电（重要！）：

SELECT * FROM data_of(NULL::pcdmet, 17);

pcdmet用任何其他表名替换呼叫中的内容。

这是如何运作的？

• anyelement是伪数据类型，多态类型，任何非数组数据类型的占位符。anyelement函数中所有出现的值都将评估为运行时提供的相同类型。通过提供已定义类型的值作为函数的参数，我们隐式定义了返回类型。

• PostgreSQL自动为每个创建的表定义一个行类型（复合数据类型），因此每个表都有一个定义明确的类型。这包括临时表，方便临时使用。

• 任何类型都可以NULL。因此，我们上交一个NULL值，将其强制转换为表类型： NULL::pcdmet 。

• 现在，该函数返回定义明确的行类型，我们可以SELECT * FROM data_of(...)用来分解行并获取单个列。

• pg_typeof(_tbl_type) 返回表的名称作为对象标识符类型regtype。当自动转换为时text，标识符会 自动加双引号，并 在需要时通过 模式限定 。因此，SQL注入是不可能的。这甚至可以处理模式修饰表的名字在那里quote_ident()会失败。