重构PL / pgSQL函数以返回各种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=' ||  ||'\r\n\r\nORDER BY Datahora;';

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

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

#1 楼

动态SQL和RETURN类型
（我把最好的留到最后，请继续阅读！）
要执行动态SQL。原则上，借助EXECUTE在plpgsql中很简单。您不需要游标-实际上，大多数情况下，最好不要使用显式游标。通过搜索在SO上查找示例。
遇到的问题：您想返回尚未定义类型的记录。函数需要使用RETURNS子句（或OUT或INOUT参数）声明返回类型。在您的情况下，您将不得不使用匿名记录，因为返回列的数量，名称和类型会有所不同。像：

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 = 
      ORDER  BY datahora'
   USING  _id;

END
$func$;

如何


变量_sensors和_type可以代替输入参数。


请注意RETURNS TABLE子句。


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


我为函数参数使用一个名称，只是为了使USING的RETURN QUERY EXECUTE子句更容易混淆。 SQL字符串中的不是引用函数参数，而是引用USING子句传递的值。 （在这个简单的示例中，两者在各自的范围内碰巧都是。） >
这种代码非常容易受到SQL注入的攻击。我使用_sensors进行保护。将变量text中的几个列名汇总在一起会阻止使用quote_ident()（通常是个坏主意！）。确保没有其他问题，例如通过_sensors单独运行列名称。想到了一个quote_ident()参数...


在PostgreSQL 9.1+中更简单
在9.1版或更高版本中，您可以使用quote_ident()进一步简化：

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

同样，可以正确地转义各个列的名称，这是一种干净的方法。
共享相同类型的可变列数
问题更新后，看起来您的返回类型具有
< br数目可变的列
，但是所有相同类型的列VARIADIC（别名format()）

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

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()  -- AS names
           , ARRAY[%s]            -- AS values
      FROM   %s
      WHERE  id = 
      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 = 
      ORDER  BY datahora'
    , pg_typeof(_tbl_type))
   USING  _id;
END
$func$ LANGUAGE plpgsql;

（重要！）：

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

用任何其他表名替换呼叫中的float8。
这是如何工作的？


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


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


任何类型都可以是pcdmet。因此，我们将一个anyelement值传递给表类型：anyelement。现在，该函数返回定义明确的行类型，我们可以使用NULL分解行并获取单个行列。


NULL返回表的名称作为对象标识符类型NULL::pcdmet。当自动转换为SELECT * FROM data_of(...)时，标识符将自动加双引号，并在需要时通过模式限定。因此，SQL注入是不可能的。这甚至可以处理pg_typeof(_tbl_type)失败的架构限定表名。

#2 楼

您可能需要返回一个游标。尝试这样的事情（我还没有尝试过）：

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

#3 楼

很抱歉，您的问题尚不清楚。但是，下面您将找到一个自包含的示例，该示例如何创建和使用返回游标变量的函数。希望对您有所帮助！

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;

#4 楼

# copy paste me into bash shell directly
clear; IFS='' read -r -d '' sql_code << 'EOF_SQL_CODE'
CREATE OR REPLACE FUNCTION func_get_all_users_roles()
  -- define the return type of the result set as table
  -- those datatypes must match the ones in the src
  RETURNS TABLE (
                 id           bigint
               , email        varchar(200)
               , password     varchar(200)
               , roles        varchar(100)) AS
$func$
BEGIN
   RETURN QUERY 
   -- start the select clause
   SELECT users.id, users.email, users.password, roles.name as roles
   FROM user_roles
   LEFT JOIN roles ON (roles.guid = user_roles.roles_guid)
   LEFT JOIN users ON (users.guid = user_roles.users_guid)
   -- stop the select clause
;
END
$func$  LANGUAGE plpgsql;
EOF_SQL_CODE
# create the function
psql -d db_name -c "$sql_code"; 

# call the function 
psql -d db_name -c "select * from func_get_all_users_roles() "