重构PL / pgSQL函数以返回各种SELECT查询的输出 [英] Refactor a PL/pgSQL function to return the output of various SELECT queries

问题描述

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

I wrote a function that outputs a PostgreSQL SELECT query well formed in text form. Now I don't want to output a text anymore, but actually run the generated SELECT statement against the database and return the result - just like the query itself would.

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 holds the list of column names for the table type. Those are declared and filled in the course of the function. Eventually, they hold values like:

• 传感器：'column1，column2，column3'
  
  除了 Datahora （ timestamp ）所有列的类型为 double precision

• sensors: 'column1, column2, column3'
  Except for Datahora (timestamp) all columns are of type double precision.

type ：'myTable'

可以是四个表之一的名称。除了 Datahora 。

基础表格的定义。

变量传感器将在类型所有 c $ c>。例如：如果 type 是 pcdmet ，那么 sensors 'datahora，dirvento，precipitacao，pressaoatm，radsolacum，tempar，umidrel，velvento'

The variable sensors will hold all columns displayed here for the corresponding table in type. For example: If type is pcdmet then sensors will be 'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'

构建存储在 result 中的 SELECT 语句。喜欢：

The variables are used to build a SELECT statement that is stored in result. Like:

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

现在，我的函数返回 text 。我复制粘贴并在pgAdmin或通过psql执行它。我想自动化，自动运行查询并返回结果。

Right now, my function returns this statement as text. I copy-paste and execute it in pgAdmin or via psql. I want to automate this, run the query automatically and return the result. How can I do that?

推荐答案

动态SQL和 RETURN 类型

^{（我保存最后一个，继续阅读！）}

以执行动态SQL 。原则上，这很简单，在plpgsql与帮助 EXECUTE 。您不需要光标 - 事实上，大部分时间您在没有显式光标的情况下更好。

通过搜索在SO上查找示例。

Dynamic SQL and RETURN type

^{(I saved the best for last, keep reading!)}
You want to execute dynamic SQL. In principal, that's simple in plpgsql with the help of EXECUTE. You don't need a cursor - in fact, most of the time you are better off without explicit cursors.
Find examples on SO with a search.

进入：您想要返回尚未定义的类型的记录。函数需要使用 <$ c $声明返回类型c> RETURNS 子句（或与 OUT 或 INOUT 参数）。在您的情况下，您必须回退到匿名记录，因为返回的列的数字，名称和类型不同。喜欢：

The problem you run into: you want to return records of yet undefined type. A function needs to declare the return type with the RETURNS clause (or with OUT or INOUT parameters). In your case you would have to fall back to anonymous records, because number, names and types of returned columns vary. Like:

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

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

However, this is not particularly useful. This way you'd have to provide a column definition list with every call of the function. Like:

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

但是，如果你事先不知道列，你怎么办？
您可以使用结构较少的文档数据类型，例如 json ， jsonb ， hstore 或 xml ：

But how would you even do this, when you don't know the columns beforehand?
You could resort to a less structured document data types like json, jsonb, hstore or xml:

• 如何存储数据表（

但是为了达到这个目的，我们可以在数据库中创建一个新的或者列表< KeyValuePair< int，Object>

But for the purpose of this question let's assume you want to return individual, correctly typed and named columns as much as possible.

列 datahora 似乎是一个给定的，我假设数据类型 timestamp 总是有两个不同名称和数据类型的列。

The column datahora seems to be a given, I'll assume data type timestamp and that there are always two more columns with varying name and data type.

我们将放弃返回类型中的通用名称。

类型，我们也会放弃，并自每个数据投放 text 类型可以转换为 text 。

Names we'll abandon in favor of generic names in the return type.
Types we'll abandon, too, and cast all to text since every data type can be cast to text.

CREATE OR REPLACE FUNCTION data_of(_id integer)
  RETURNS TABLE (datahora timestamp, col2 text, col3 text) 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$ LANGUAGE plpgsql;

这如何工作？

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

  How does this work?

  • The variables _sensors and _type could be input parameters instead.

    请注意 RETURNS TABLE 子句。

    Note the RETURNS TABLE clause.

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

    Note the use of RETURN QUERY EXECUTE. That is one of the more elegant ways to return rows from a dynamic query.

    我使用函数参数的名称，只是使 USING 的子句RETURN QUERY EXECUTE 较少混淆。在SQL字符串中的 $ 1 不是引用函数参数，而是引用 USING 子句传递的值。 （

    I use a name for the function parameter, just to make the USING clause of RETURN QUERY EXECUTE less confusing. $1 in the SQL-string does not refer to the function parameter but to the value passed with the USING clause. (Both happen to be $1 in their respective scope in this simple example.)

    请注意示例 _sensors 的值：

    Note the example value for _sensors: each column is cast to type text.

    此类代码非常容易受到 SQL注入 。我使用 quote_ident（） 以防范。将变量 _sensors 中的几个列名称汇总在一起可防止使用 quote_ident（）（通常是错误的理念！）。确保没有坏东西可以在那里有一些其他的方式，例如通过单独运行列名称通过 quote_ident（）改为。 A VARIADIC 参数...

    This kind of code is very vulnerable to SQL injection. I use quote_ident() to protect against it. Lumping together a couple of column names in the variable _sensors prevents the use of quote_ident() (and is typically a bad idea!). Ensure that no bad stuff can be in there some other way, for instance by individually running the column names through quote_ident() instead. A VARIADIC parameter comes to mind ...

    对于版本9.1或更高版本，您可以使用 format（） 以进一步简化：

    With version 9.1 or later you can use format() to further simplify:

    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;
    

    同样，单独的列名称可以正确转义，并且是干净的方式。

    Again, individual column names could be escaped properly and would be the clean way.

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

    After your question updates it looks like your return type has

    
    
    • 一个变量 列
    
    
    • / em> double precision （别名 float8 ）
    
    
    • a variable number of columns
    • but all columns of the same type double precision (alias float8)

    由于我们要定义函数的 RETURN 类型，我使用 ARRAY 在这种情况下，它可以保存可变数量的值。此外，我返回一个数组的列名，所以你可以解析结果中的名称：

    As we have to define the RETURN type of a function I resort to an ARRAY type in this case, which can hold a variable number of values. Additionally, I return an array with column names, so you could parse the names out of the result, too:

    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;
    

    
    

    如果您实际尝试返回表格的所有列（例如表格，然后使用这个简单，非常强大的解决方案与 多态类型 ：

    If you are actually trying to return all columns of a table (for instance one of the tables at the linked page, then use this simple, very powerful solution with a polymorphic type:

    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

    Replace pcdmet in the call with any other table name.

    
    

    • anyelement 是伪数据类型，多态类型，任何非数组数据类型的占位符。函数中所有出现的 anyelement 计算为在运行时提供的相同类型。

    • anyelement is a pseudo data type, a polymorphic type, a placeholder for any non-array data type. All occurrences of anyelement in the function evaluate to the same type provided at run time. By supplying a value of a defined type as argument to the function, we implicitly define the return type.

    PostgreSQL自动定义一个行类型（一个复合数据类型），因此每个表都有一个明确定义的类型。

    PostgreSQL automatically defines a row type (a composite data type) for every table created, so there is a well defined type for every table. This includes temporary tables, which is convenient for ad-hoc use.

    任何类型都可以 NULL 。因此，我们递交一个 NULL 值，转换为表类型。

    Any type can be NULL. So we hand in a NULL value, cast to the table type.

    良好定义的行类型，我们可以使用 SELECT * FROM data_of（...）来分解行并获取单独的列。

    Now the function returns a well-defined row type and we can use SELECT * FROM data_of(...) to decompose the row and get individual columns.

    pg_typeof（_tbl_type） 返回表的名称对象标识符类型 regtype 。当自动转换为 text 时，如果需要，标识符会自动双重引用并符合架构资格。因此，SQL注入是不可能的。这甚至可以处理模式限定的表名其中 quote_ident（）将失败。

    pg_typeof(_tbl_type) returns the name of the table as object identifier type regtype. When automatically converted to text, identifiers are automatically double-quoted and schema-qualified if needed. Therefore, SQL injection is not a possible. This can even deal with schema-qualified table-names where quote_ident() would fail.

    这篇关于重构PL / pgSQL函数以返回各种SELECT查询的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！