从 plpgsql 函数返回一组行. [英] Returning set of rows from plpgsql function.

问题描述

我想从 plpgsql 函数返回表.

I would like to return table from plpgsql function.

这是我的代码.

    CREATE FUNCTION test() RETURNS my_table AS
    $BODY$DECLARE
        q4 my_table;
    BEGIN
        q4 := SELECT * FROM my_table;
        RETURN q4;
    END;$BODY$
    LANGUAGE sql;

我收到以下错误:

    Error: ERROR:  syntax error at or near "SELECT"
    LINE 5:  q4 := SELECT * FROM my_table;

我从这个问题/教程开始.https://dba.stackexchange.com/questions/35721/声明变量表类型在 pl-pgsql &&http://postgres.cz/wiki/PL/pgSQL_%28en%29

I started from this questions/tutorials. https://dba.stackexchange.com/questions/35721/declare-variable-of-table-type-in-pl-pgsql && http://postgres.cz/wiki/PL/pgSQL_%28en%29

这个想法是我需要将此查询分配给一个变量.这只是我想创建的功能的一小部分.

The idea is that I need to assign this query to a variable. This is only small part of function that I would like to create.

第二个问题将是如何遍历该集合并进行一些数学运算并为该表的某些字段赋值.但是首先我想解决这个问题.

The second problem will be how to iterate through that set and make some mathematical operations and assigning value to some field of that table. However firstly I would like to solve this problem.

推荐答案

CREATE FUNCTION test() 
RETURNS my_table AS
$BODY$
DECLARE
    q4 my_table;
BEGIN
    -- add brackets to get a value 
    -- select row as one value, as q4 is of the type my_table
    -- and limit result to one row
    q4 := (SELECT my_table FROM my_table ORDER BY 1 LIMIT 1);
    RETURN q4;
END;$BODY$
-- change language to plpgsql
LANGUAGE plpgsql;

• 您不能在 sql 函数中使用变量，请使用 plpgsql.
• 您可以为变量分配单个值，而 select query 返回一组行.
• 您必须选择一行作为一个值，因为该变量是复合类型.
• You cannot use variables in sql functions, use plpgsql.
• You can assign single value to a variable, while select query returns set of rows.
• You have to select a row as one value, as the variable is of composite type.

使用循环的示例:

DROP FUNCTION test();
CREATE FUNCTION test() 
-- change to SETOF to return set of rows, not a single row
RETURNS SETOF my_table AS
$BODY$
DECLARE
    q4 my_table;
BEGIN
    FOR q4 in
        SELECT * FROM my_table
    LOOP
        RETURN NEXT q4;
    END LOOP;
END;$BODY$
LANGUAGE plpgsql;

SELECT * FROM test();

阅读有关从函数返回的文档

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