plpgsql function returns table(..)

Question

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

Erwin Brandstetter · Accepted Answer · 2011-12-24 19:27:39Z

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

Collectives™ on Stack Overflow

plpgsql function returns table(..)

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related