从PostgreSQL函数返回SETOF行 [英] Return SETOF rows from PostgreSQL function

问题描述

我遇到一种情况，我想返回两个视图之间的联接.那就是很多专栏.在sql server中，这非常容易.但是在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".

有什么办法可以绕过它，我不想提供返回列的定义.

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$

推荐答案

消毒功能

手册具有PL/pgSQL的所有基础知识.基本上，您所拥有的可以简化/清理为:

Sanitize function

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

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;

• 您不需要 BEGIN .. END的其他实例在功能体中 ，只是用自己的作用域启动一个单独的代码块，这很少需要.

  • 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.

    标准SQL串联运算符为||. +是您以前供应商的创造性"补充.

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

    请勿使用 CaMeL-case标识符，除非您将其双引号引起来.最好不要使用它们:

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

    • PostgreSQL列名是否区分大小写?

    varchar(4000)也针对SQL Server的特定限制进行了定制.在Postgres中，此数据类型没有任何性能优势.仅当您实际需要4000个字符的限制时才使用它.我只会使用 text -只是我们不这样做在简化函数之后，这里根本不需要任何变量 .

    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.

    如果您尚未使用format()，请在此处咨询手册.

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

    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:

    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
    ...
    

    或提供带有(最简单的)RETURNS TABLE的列定义列表:

    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.

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

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

    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$;
    

    对于完全动态的查询，我宁愿使用普通的SQL查询开始.不是功能.

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

    还有更多高级选项，但是您可能需要先学习基础知识.

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

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

    这篇关于从PostgreSQL函数返回SETOF行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！