重构一个PL/pgSQL函数，以返回各种选择查询的输出。

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.

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

What I have so far:

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:

传感器保存表类型的列名列表。这些是在函数过程中声明和填充的。最终，他们的价值观是:

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

  传感器:“column1, column2, column3”，除了Datahora (timestamp)，所有列都是双精度的。

• type :'myTable'
  Can be the name of one of four tables. Each has different columns, except for the common column Datahora.

  类型:“myTable”可以是四个表中的一个的名称。除了公共列Datahora之外，每个列都有不同的列。

Definition of the underlying tables.

底层表的定义。

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'

变量传感器将保存在这里显示的对应表的所有列的类型。例如:如果类型是pcdmet，那么传感器将是“datahora,dirvento, acao,pressaoatm,radsolacum,tempar,umidrel,velvento”。

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

变量用于构建存储在result中的SELECT语句。如:

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

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?

现在，我的函数以文本的形式返回这个语句。我复制粘贴并通过pgAdmin或psql执行它。我想自动执行，自动运行查询并返回结果。我怎么做呢?

3 个解决方案

#1

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.

(我把最好的留到最后，继续阅读!)您需要执行动态SQL。在原则上，在执行的帮助下，这在plpgsql中很简单。您不需要游标——事实上，在大多数情况下，如果没有显式游标，情况会更好。在搜索中找到这样的例子。

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:

您遇到的问题是:您希望返回尚未定义类型的记录。函数需要使用return子句(或带OUT或INOUT参数)声明返回类型。在您的情况下，您将不得不退回到匿名记录，因为返回列的数量、名称和类型各不相同。如:

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

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:

但是，当你事先不知道列数时，你怎么做呢?您可以使用不太结构化的文档数据类型，如json、jsonb、hstore或xml:

• How to store a data table (or List<KeyValuePair<int,Object>>, or Dictionary) in database?
• 如何在数据库中存储数据表(或列表 >，或字典)?

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

但是为了这个问题的目的，我们假设您希望尽可能多地返回独立的、正确输入的和命名的列。

Simple solution with fixed return type

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.

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

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;

How does this work?

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

  变量_sensor和_type可以作为输入参数。

• Note the RETURNS TABLE clause.

  注意返回表子句。

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

  注意返回查询执行的使用。这是从动态查询返回行的一种更优雅的方式。

• 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.)

  我使用函数参数的名称，只是为了使RETURN查询的USING子句执行起来不那么混乱。sql字符串中的$1不引用函数参数，而是引用使用子句传递的值。(在这个简单的例子中，两者在各自的范围内都是1美元。)

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

  注意_sensor的示例值:每一列都被转换为文本类型。

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

  这种代码非常容易受到SQL注入的影响。我使用quote_ident()来保护它。在变量_传感器中集成几个列名可以防止quote_ident()的使用(而且通常是一个坏主意!)确保不存在其他不良内容，例如通过quote_ident()单独运行列名。我们想到了一个可变参数……

Simpler with PostgreSQL 9.1+

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

对于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;

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

同样，可以正确地转义单个列名，这是一种干净的方式。

Variable number of columns sharing the same type

After your question updates it looks like your return type has

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

• a variable number of columns
• 列数可变
• but all columns of the same type double precision (alias float8)
• 但是相同类型的所有列都是双精度的(别名float8)

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;

Various complete table types

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;

Call:

电话:

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

Replace pcdmet in the call with any other table name.

在调用中替换pcdmet，并使用任何其他表名。

How does this work?

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

  anyelement是伪数据类型、多态类型、任何非数组数据类型的占位符。函数中的anyelement的所有事件都在运行时对同一类型进行评估。通过向函数提供定义类型的值作为参数，我们隐式地定义了返回类型。

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

  PostgreSQL为创建的每个表自动定义一个行类型(复合数据类型)，因此每个表都有一个定义良好的类型。这包括临时表，方便临时使用。

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

  任何类型都可以为空。因此我们将NULL值转换为表类型。

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

  现在，函数返回一个定义良好的行类型，我们可以使用data_of(…)中的SELECT *来分解行并获得单独的列。

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

  pg_typeof(_tbl_type)返回表的名称作为对象标识符类型regtype。当自动转换为文本时，如果需要，标识符会自动被双引号和模式限定。因此，SQL注入是不可能的。这甚至可以处理那些符合模式的表名，其中quote_ident()将会失败。

#2

You'll probably want to return a cursor. Try something like this (I haven't tried it):

您可能希望返回一个游标。试试这样(我没试过):

CREATE OR REPLACE FUNCTION data_of(integer)
  RETURNS refcursor AS
$BODY$
DECLARE
      --Declaring variables
      ref refcursor;
BEGIN
      -- make sure `sensors`, `type`, $1 variable has valid value
      OPEN ref FOR 'SELECT Datahora,' || sensors ||
      ' FROM ' || type ||
      ' WHERE nomepcd=' || $1 ||' ORDER BY Datahora;';
      RETURN ref;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION data_of(integer) OWNER TO postgres;

#3

I'm sorry to say but your question is very unclear. However below you'll find a self contained example how to create and use a function that returns a cursor variable. Hope it helps !

很抱歉，你的问题很不清楚。但是在下面，您将找到一个自包含的示例，如何创建并使用一个返回游标变量的函数。希望它可以帮助!

begin;

create table test (id serial, data1 text, data2 text);

insert into test(data1, data2) values('one', 'un');
insert into test(data1, data2) values('two', 'deux');
insert into test(data1, data2) values('three', 'trois');

create function generate_query(query_name refcursor, columns text[])
returns refcursor 
as $$
begin
  open query_name for execute 
    'select id, ' || array_to_string(columns, ',') || ' from test order by id';
  return query_name;
end;
$$ language plpgsql;

select generate_query('english', array['data1']);
fetch all in english;

select generate_query('french', array['data2']);
fetch all in french;
move absolute 0 from french; -- do it again !
fetch all in french;

select generate_query('all_langs', array['data1','data2']);
fetch all in all_langs;

-- this will raise in runtime as there is no data3 column in the test table
select generate_query('broken', array['data3']);

rollback;