从PostgreSQL函数返回SETOF行

Question

从PostgreSQL函数返回SETOF行

postgresqlplpgsqldynamic-sqlpostgresql-9.2

17

我有这样一种情况，我想返回两个视图之间的连接，但是有很多列。在SQL Server中很容易实现。但是在PostgreSQL中，当我执行连接时，会出现错误“需要列定义列表”。

是否有任何方法可以绕过此错误，我不想提供返回列的定义。

CREATE OR REPLACE FUNCTION functionA(username character varying DEFAULT ''::character varying, databaseobject character varying DEFAULT ''::character varying)
  RETURNS SETOF ???? AS
$BODY$
Declare 
SqlString varchar(4000) = '';
BEGIN
IF(UserName = '*') THEN
   Begin
   SqlString  := 'select * from view1 left join ' + databaseobject  + ' as view2 on view1.id = view2.id';
   End;
ELSE
    Begin
    SqlString := 'select * from view3 left join ' + databaseobject  + ' as view2 on view3.id = view2.id';
    End;
END IF; 
execute (SqlString  );
END;
$BODY$

- user433023

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Erwin Brandstetter · Accepted Answer

过滤函数

你目前拥有的可以简化/过滤为：

CREATE OR REPLACE FUNCTION func_a (username text = '', databaseobject text = '')
  RETURNS ????
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format ('SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)'
         , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END
         , databaseobject);
END
$func$;

在函数体中只需要添加额外的 BEGIN ... END 实例，就可以开始具有自己作用域的单独代码块，但这种情况很少见。

标准的SQL连接运算符是||。而+是你以前供应商的“创意”之一。

不要使用驼峰式标识符，除非你将它们用双引号引起来。最好根本不使用它们，请参阅以下内容：

PostgreSQL列名区分大小写吗?

varchar(4000)也是针对SQL Server特定限制量身定制的。在Postgres中没有具体意义。如果您确实需要一个4000字符的限制，请使用varchar(4000)。我会使用text——因为我们在简化函数后根本不需要任何变量。

如果您还没有使用format()，请参考此处的手册。

返回类型

现在回答您的实际问题：动态查询的返回类型可能很棘手，因为SQL要求最迟在调用时声明。如果您的数据库中已有匹配列定义列表的表、视图或复合类型，可以直接使用它们：

CREATE FUNCTION foo()
  RETURNS SETOF my_view AS
...

否则，将列定义列表简单地用 RETURNS TABLE 拼写：

CREATE FUNCTION foo()
  RETURNS TABLE (col1 int, col2 text, ...) AS
...

如果您正在按照自己的方式创建行类型，您可以返回匿名记录：

CREATE FUNCTION foo()
  RETURNS SETOF record AS
...

但是这样你每次调用都需要提供列定义列表，所以我几乎从不使用。

我一开始就不会使用SELECT *。使用一个明确的列列表来返回并相应地声明返回类型：

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS TABLE(col1 int, col2 text, col3 date)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format ($f$SELECT v1.col1, v1.col2, v2.col3
              FROM %s v1 LEFT JOIN %I v2 USING (id)$f$
         , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END
         , databaseobject);
END
$func$;

对于完全动态的查询，考虑在客户端中构建查询，而不是使用函数。

你需要先了解基础知识:

然后有更高级的选项可以使用多态类型，允许您在调用时传递返回类型，详见最后一章节:

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