Return Next and Return Query

2009/12/23 Wappler, Robert <rwappler@ophardt.com>:

Hello,

I’m not quite sure, what’s the difference between RETURN NEXT and RETURN
QUERY.

From the documentation (Sec. 38.6) RETURN NEXT returns a table and in my
understanding with each execution a different table.

it is wrong.

RETURN QUERY appends a result set of a query to the function’s result set,
i.e. the function returns at most one table.

RETURN QUERY and RETURN NEXT are almost identical.

RETURN NEXT record push one tuple to function's result set, RETURN
QUERY push setof tuples to function's result set. Both RETURNS are not
final, so you can repeat call both.

BEGIN
RETURN QUERY SELECT * FROM tab WHERE a = 1;
RETURN QUERY SELECT * FROM tab WHERE a = 2;
RETURN;
END;

is correct.

Regards
Pavel Stehule

Where is the difference for the caller? That means: how to determine if one
or several tables have been returned?

RETURN NEXT variable -- it push one tuple on output
RETURN QUERY query -- it push result of query on output