PostgreSQL stored Procedure (4)-return statement

1. Return statement

There are three commands that can be used to return data from a function:

• RETURN
• RETURN NEXT
• RETURN QUERY

2. Return command syntax:

 
 
RETURN
RETURN expression;

If you do not use the expression return command to tell the function that it has finished executing.
If you return a scalar type, you can use any expression. To return a composite (row) value, you must write an expression of a record or a row variable.

2.1 Case 1: With an expression return case

 
 
CREATE OR REPLACE FUNCTION getCompFoo1
(in_col1 int, in_col2 TEXT)
RETURNS compfoo
AS $$
DECLARE 
    lottu compfoo;
BEGIN
    lottu.col1 := in_col1 + 1;
    lottu.col2 := in_col2 || ‘_result‘;
    RETURN lottu;
END;
$$ LANGUAGE PLPGSQL;

If you declare a function with an output parameter, you only need to write a return without an expression. Then the current value of the output parameter variable is returned.

 
CREATE OR REPLACE FUNCTION getCompFoo2
(in_col1 IN int,in_col2 IN TEXT,
 out_col1 OUT int, out_col2 OUT TEXT)
AS $$
BEGIN
    out_col1 := in_col1 + 1;
    out_col2 := in_col2 || ‘_result‘;
END;
$$ LANGUAGE PLPGSQL;

2.2 Case 2: Return case without an expression

If you declare a function to return void, a return statement can be used to exit the function prematurely, but do not write an expression after return.

 
 
CREATE OR REPLACE FUNCTION getreturn(in_col1 int)
RETURNS void
AS $$
BEGIN
    if in_col1 > 0 then
      RAISE NOTICE ‘there is %‘,in_col1;
    else
      return;
    end if;
END;
$$ LANGUAGE PLPGSQL;

3. RETURN Next command syntax:

RETURN NEXT expression;

3.1 RETURN Next command

can be used for scalar and composite data types, and for composite types, a complete result "table" is returned.

 
CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, ‘three‘);
INSERT INTO foo VALUES (4, 5, ‘six‘);

CREATE OR REPLACE FUNCTION getAllFoo() RETURNS SETOF foo AS
$$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$$ LANGUAGE PLPGSQL;

4. RETURN Query command syntax:

 
RETURN QUERY query;
RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ];

4.1 RETURN Query Command

Appends the result of a query to the result set of a function.

CREATE OR REPLACE FUNCTION getAllFoo2() RETURNS SETOF foo AS
$$
DECLARE
    r foo%rowtype;
BEGIN
    RETURN QUERY SELECT * FROM foo WHERE fooid > 0;
END
$$ LANGUAGE PLPGSQL;

4.2 RETURN QUERY Execute command

is to execute dynamic SQL.

 
CREATE OR REPLACE FUNCTION getAllFoo3(filter numeric) RETURNS SETOF foo AS
$$
BEGIN
    RETURN QUERY EXECUTE ‘SELECT * FROM foo WHERE fooid > $1
   USING filter;
;
END
$$ LANGUAGE PLPGSQL;

PostgreSQL stored Procedure (4)-return statement