返回PostgreSQL函数的行。

I have a situation where I want to return the join between two views. and that's a lot of columns. It was pretty easy in sql server. But in PostgreSQL when I do the join. I get the error "a column definition list is required".

我有一种情况，我想返回两个视图之间的连接。有很多列。在sql server中非常简单。但是在PostgreSQL中，当我执行连接时。我得到了“需要列定义列表”的错误。

Is there any way I can bypass this, I don't want to provide the definitions of returning columns.

有没有办法绕过这个，我不想提供返回列的定义。

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$

1 个解决方案

#1

Sanitize function

The manual has all the basics for PL/pgSQL. Basically, what you have can be simplified / sanitized to:

该手册包含PL/pgSQL的所有基础知识。基本上，你所拥有的可以简化/消毒为:

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS ???? 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$  LANGUAGE plpgsql;

• You don't need additional instances of BEGIN .. END in the function body except to start a separate code block with its own scope, which is rarely needed.

  你不需要额外的BEGIN实例。在函数体中结束，除了用它自己的作用域启动一个单独的代码块之外，这是很少需要的。

• The standard SQL concatenation operator is ||. + is a "creative" addition of your former vendor.

  标准的SQL连接操作符是||。+是你以前的供应商的“创意”补充。

• Don't use CaMeL-case identifiers unless you double-quote them. Best you don't use them at all:

  不要使用CaMeL-case标识符，除非您重复引用它们。最好不要使用它们:

  • Are PostgreSQL column names case-sensitive?
  • PostgreSQL列名称是否区分大小写?

• varchar(4000) is also tailored to a specific limitation of SQL Server. This data type has no performance benefit whatsoever in Postgres. Only use it if you actually need a limit of 4000 characters. I would just use text - except that we don't need any variables at all here, after I simplified the function.

  varchar(4000)也适用于SQL Server的特定限制。这种数据类型在Postgres中没有任何性能优势。仅当您实际需要4000个字符时才使用它。我只需要使用文本——除了我们不需要任何变量，在我简化函数之后。

• If you have not used format(), yet, consult the manual here.

  如果您还没有使用format()，请参阅这里的手册。

Return type

Now, for your actual question: The return type for a dynamic query is a bit tricky, since SQL requires the function to return a well defined type. If you have a table or view or composite type in your database already that matches the column definition list you want to return you can just use that:

现在，对于您的实际问题:动态查询的返回类型有点棘手，因为SQL要求函数返回一个定义良好的类型。如果您的数据库中已经有一个与您想要返回的列定义列表匹配的表或视图或组合类型，您可以使用以下方法:

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

If you are making the type up as you go, you can either return anonymous records:

如果您正在创建该类型，您可以返回匿名记录:

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

or provide a column definition list with (simplest) RETURNS TABLE:

或提供列定义列表(最简单)返回表:

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

The downside for anonymous records: you then have to provide a column definition list with every call, so I hardly ever use that.

匿名记录的缺点是:您必须为每次调用提供一个列定义列表，因此我几乎从不使用它。

I wouldn't use SELECT * to begin with. Use a definitive list of columns to return and declare your return type accordingly:

我不会用SELECT *开头。使用确定的列列表返回并相应地声明返回类型:

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS TABLE(col1 int, col2 text, col3 date) AS
$func$
BEGIN

RETURN QUERY EXECUTE
format ('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$;

For completely dynamic queries, I'd rather use a plain SQL query to begin with. Not a function.

对于完全动态的查询，我宁愿使用纯SQL查询作为开始。不是一个函数。

There are more advanced options, but you may need to study the basics first.

有更高级的选项，但是您可能需要首先学习基础知识。

• Refactor a PL/pgSQL function to return the output of various SELECT queries
• 重构一个PL/pgSQL函数，以返回各种选择查询的输出。