Help Center > > Developer Guide> Stored Procedures> Basic Statements> Return Statement

Return Statement

Updated at: Jul 15, 2020 GMT+08:00

Syntax

Figure 1 shows the syntax diagram for a return statement.

Figure 1 return_clause::=

Details about the syntax diagram are as follows:

This statement returns control from a stored procedure to a caller.

Examples

See Examples for call statement examples.

Syntax of a Storage Function

RETURN NEXT and RETURN QUERY are used for functions and cannot be used for stored procedures.

When creating a function, specify SETOF datatype for the return values.

return_next_clause::=

return_query_clause::=

Details about the syntax diagram are as follows:

If a function needs to return a result set, use RETURN NEXT or RETURN QUERY to add results to the result set, and then continue to execute the next statement of the function. As the RETURN NEXT or RETURN QUERY statement is executed repeatedly, more and more results will be added to the result set. After the function is executed, all results are returned.

RETURN NEXT can be used for scalar and compound data types.

RETURN QUREY executes a query and adds the query result to the result set of the function. RETURN QUERY has a variant RETURN QUERY EXECUTE. You can add dynamic queries and add parameters to the queries by using USING.

Example of a Memory Function

postgres=# CREATE TABLE t1(a int);
postgres=# INSERT INTO t1 VALUES(1),(10);

--RETURN NEXT
CREATE OR REPLACE FUNCTION fun_for_return_next() RETURNS SETOF t1 AS $$
DECLARE
   r t1%ROWTYPE;
BEGIN
   FOR r IN select * from t1
   LOOP
      RETURN NEXT r;
   END LOOP;
   RETURN;
END;
$$ LANGUAGE PLPGSQL;
postgres=# call fun_for_return_next();
 a
---
 1
 10
(2 rows)

-- RETURN QUERY
CREATE OR REPLACE FUNCTION fun_for_return_query() RETURNS SETOF t1 AS $$
DECLARE
   r t1%ROWTYPE;
BEGIN
   RETURN QUERY select * from t1;
END;
$$
language plpgsql;
postgres=# call fun_for_return_next();
 a
---
 1
 10
(2 rows)

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel