postgresql - 从 PostgreSQL 函数返回 SETOF 行

Question

我有一种情况想要返回两个 View 之间的连接。这是很多专栏。这在 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$

Answer 1

清理函数

您当前拥有的可以简化/净化为:

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 in the function body 的额外实例以它们自己的范围启动单独的代码块，这很少需要。

标准的 SQL 连接运算符是 ||。 + 是您以前供应商的“创造性”添加。

不要使用 CaMeL-case identifiers除非你双引号他们。最好根本不要使用它们，请参阅:

• Are PostgreSQL column names case-sensitive?

varchar(4000) 也针对 SQL Server 的特定限制进行了定制。它在 Postgres 中没有特殊意义。如果您确实需要 4000 个字符的限制，则仅使用 varchar(4000)。我只会使用 text - 除了我们在简化函数之后根本不需要任何变量。

如果你还没有使用过format()，consult the manual here .

返回类型

现在，对于您的实际问题:动态查询的返回类型可能很棘手，因为 SQL 要求最迟在调用时声明它。如果你的数据库中有一个表或 View 或复合类型已经匹配列定义列表，你可以使用它:

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

否则，用 out 拼写列定义列表(最简单的)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$;

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

您需要先了解基础知识:

• Refactor a PL/pgSQL function to return the output of various SELECT queries
• PL/pgSQL in the Postgres manual

然后有更多高级选项 polymorphic types ，它允许您在调用时传递返回类型。更多在最后一章:

• Refactor a PL/pgSQL function to return the output of various SELECT queries