What is the difference between RETURN NEXT
and RETURN RECORD
in PL/pgSQL? I find the manual quite confusing in explaining what RETURN RECORD
actually does. What is a RECORD
type?
2 Answers
While RETURN NEXT
is valid plpgsql syntax, is non-existent as such. It corresponds to how you declare the return type in the function header. Read the manual about how to return from a function. RETURN RECORD
In a function declaring RETURNS int
you can:
RETURN 1;
For RETURNS SETOF int
you can:
RETURN NEXT 1;
If you declare OUT
parameters, you can just:
RETURN;
-- or do nothing: OUT params are returned at the end of the function anyway
Or if you combine OUT
parameters with RETURNS SETOF record
- effectively the same as RETURNS TABLE (...)
:
RETURN NEXT;
The current state of all OUT
parameters is returned as next row.
Finally, to return a whole set of rows at once:
RETURN QUERY ...
record
is a special data type. It's meaning depends on where you use it. Like I posted in my answer to your previous question:
A RETURN NEXT statement:
can append zero or more rows to the function's result set one by one and the rows can be modified one by one.
cannot exit a function while a RETURN statement can.
can work only in a PL/pgSQL function.
can work only with
SETOF <sometype>
orTABLE()
in aRETURNS
clause otherwise there are the error and the error.
*The doc explains RETURN NEXT
statement in detail.
A RETURN QUERY statement:
can append zero or more rows to the function's result set at once and the rows cannot be modified.
cannot exit a function while a RETURN statement can.
can be used with INSERT, UPDATE or DELETE statement with RETURNING clause.
can be used with EXECUTE statement to be dynamic.
can work only in a PL/pgSQL function.
can work only with
SETOF <sometype>
orTABLE()
in aRETURNS
clause otherwise there are the error and the error.
*The doc explains RETURN QUERY
statement in detail.