plpgsql function returns table(..)

15
votes

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

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

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

1
postgresqlplpgsql

1 Answers

43
votes

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;

Call:

SELECT * FROM foo(10);