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

Question

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

我编写了一个输出PostgreSQL的函数SELECT查询格式良好的文本形式。现在我不想输出文本了，但实际上运行生成的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 variablesBEGIN
      -- 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'
  可以是四个表之一的名称。每个列都有不同的列，但公共列除外。Datahora.

底层表的定义.

变量sensors将举行全中相应表的列显示在此处。type..例如：如果type是pcdmet然后sensors将是'datahora,dirvento,precipitacao,pressaoatm,radsolacum,tempar,umidrel,velvento'

变量用于构建SELECT中存储的语句。result..比如：

SELECT Datahora, column1, column2, column3FROM   myTableWHERE  id=20ORDER  BY Datahora;

现在，我的函数返回以下语句text..我复制粘贴并在pgAdmin或psql中执行。我想自动化这一点，自动运行查询并返回结果。我怎么能这么做？

Answer 1

您可能希望返回一个光标..试试这样的东西(我还没试过)：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;

Answer 2

我很抱歉地说，你的问题很不清楚。但是，下面将找到一个自包含的示例，说明如何创建和使用返回游标变量的函数。希望能帮上忙！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 tableselect generate_query('broken', array['data3']);rollback;