postgresql plpgsql 函数返回表(..)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4279876/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
plpgsql function returns table(..)
提问by David Dias
I'm trying to get this plpgsql function to work:
我试图让这个 plpgsql 函数工作：
CREATE OR REPLACE FUNCTION outofdate(actualdate varchar)
RETURNS TABLE(designacion varchar(255),timebeingrotten varchar(255))
AS $BODY$
SELECT designacao, actualdate - prazo
FROM alimento
WHERE prazo < actualdate;
$BODY$
LANGUAGE 'plpgsql' volatile;
SELECT *
From outofdate('12/12/2012');
It keeps giving me an error on line 2 - table ..
它一直在第 2 行 - 表上给我一个错误..
ERROR: syntax error at or near "TABLE" LINE 2: RETURNS TABLE(designacion varchar(255),timebeingrotten varch... ^
***Error ***
ERROR: syntax error at or near "TABLE" SQL state: 42601 Character: 67
错误：“表格”第 2 行或附近的语法错误：返回表格（designacion varchar(255),timebeingrotten varch... ^
***错误 ** *
错误：“TABLE”SQL 状态处或附近的语法错误：42601 字符：67
回答by Pavel Stehule
I am not sure, but maybe you use a older version of pg without support of RETURNS TABLEsyntax. Next problem in your example is wrong syntax for PL/pgSQL language - look to manual for syntax - every function must contain a block with BEGIN ... END. Records can be returned via RETURN QUERYstatement. Have a look at this tutorial.
我不确定，但也许您在不支持RETURNS TABLE语法的情况下使用旧版本的 pg 。您示例中的下一个问题是 PL/pgSQL 语言的错误语法 - 查看语法手册 - 每个函数都必须包含一个带有BEGIN ... END. 记录可以通过RETURN QUERY语句返回。看看这个教程。
CREATE OR REPLACE FUNCTION foo(a int)
RETURNS TABLE(b int, c int) AS $$
BEGIN
RETURN QUERY SELECT i, i+1 FROM generate_series(1, a) g(i);
END;
$$ LANGUAGE plpgsql;
Call:
称呼：
SELECT * FROM foo(10);
