RETURN Statements

In openGauss, data can be returned in either of the following ways: RETURN, RETURN NEXT, or RETURN QUERY. RETURN NEXT and RETURN QUERY are used only for functions and cannot be used for stored procedures.

RETURN

Syntax

Figure 1 shows the syntax diagram for a return statement.

Figure 1 return_clause::=

The above syntax diagram is explained as follows:

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

Examples

See Examples for call statement examples.

RETURN NEXT and RETURN QUERY

Syntax

When creating a function, specify SETOFdatatype for the return values.

return_next_clause::=

return_query_clause::=

The above syntax diagram is explained 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 QUERY has a variant RETURN QUERY EXECUTE. You can add dynamic queries and add parameters to the queries by USING.

Examples

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

--RETURN NEXT
openGauss=# 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;
openGauss=# call fun_for_return_next();
 a
---
 1
 10
(2 rows)

-- RETURN QUERY
openGauss=# 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;
openGauss=# call fun_for_return_query();
 a
---
 1
 10
(2 rows)