plpgsql函数返回表(..) [英] plpgsql function returns table(..)

问题描述

我正在尝试使此plpgsql函数起作用:

I'm trying to get this plpgsql function to work:

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');

它总是在第2行-table ..

It keeps giving me an error on line 2 - table ..

错误:语法错误在或附近 表格"第2行:退货 表(设计) varchar(255)，timebeingrotten varch ... ^

ERROR: syntax error at or near "TABLE" LINE 2: RETURNS TABLE(designacion varchar(255),timebeingrotten varch... ^

*** 错误 ** *

*** Error ***

错误:"TABLE"处或附近的语法错误 SQL状态:42601字符:67

ERROR: syntax error at or near "TABLE" SQL state: 42601 Character: 67

推荐答案

我不确定，但是也许您使用的是较旧版本的pg，却不支持RETURNS TABLE语法.您的示例中的下一个问题是PL/pgSQL语言的语法错误-语法请参见手册-每个函数必须包含一个带有BEGIN ... END的块.记录可以通过RETURN QUERY语句返回.看看此教程.

I am not sure, but maybe you use a older version of pg without support of RETURNS TABLE syntax. 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 QUERY statement. Have a look at this tutorial.

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;

致电:

SELECT * FROM foo(10);

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