Using `RETURN NEXT` and `RETURN QUERY` in the same Postgres function

Question

I am a newbie in PostgreSQL (using v9.0) and wanted to know if using return next and Return query in the same function is possible without exiting the function before attaching the results in the resultset?

CREATE TYPE return_type AS
   (paramname character varying,
    value character varying);

CREATE OR REPLACE FUNCTION myfuntion(param1 character varying)
RETURNS SETOF return_type AS
declare
r return_type;
message varchar;
status integer;
$BODY$
BEGIN
o_call_status := 0;
o_call_message := '';
        Return query Select 'mystatus' as paramName, status::varchar as value;
        Return query Select 'mymessage' as paramName, message as value;

       for r in SELECT 'mycolumnname1' as paramName,mycolumn1 as value FROM tb1 
                WHERE column1 = val
                UNION ALL
                SELECT 'mycolumnname2' as paramName,mycolumn2 as value  FROM tb1 
                WHERE column1 = val
                UNION ALL
                SELECT 'mycolumnname3' as paramName,mycolumn3 as value  FROM tb2 
                WHERE column1 = val1 AND
                column4 = val4  loop
          return next r;
        end loop;

        END;
        $BODY$
LANGUAGE plpgsql VOLATILE

Deepfreeze

Answer 1

Returning from a PL/pgSQL function

You can mix RETURN NEXT and RETURN QUERY freely and repeatedly. The underlying principle is that PL/pgSQL builds the table locally and does not return until the function is finished:

The manual:

Note: The current implementation of RETURN NEXT and RETURN QUERY stores the entire result set before returning from the function, ...

You can raise an exception to abort the operation if you are unsatisfied with the results so far and the client won't see a thing. We also included an example in the manual demonstrating this, just above said quote.

Cross tabulation

As for what you are trying do achieve, consider the crosstab() function from the tablefunc extension. See:

• PostgreSQL Crosstab Query

Lucio

Thanks @Erwin, that web page in the documentation was exactly the explanation I needed to understand when to use those keywords. So the answer is only when used the return keyword like this return; will cause the termination of the function. – Deepfreeze

@Douglas: Yes. And when the function reaches the end without explicit RETURN, the answer (if any) is sent as well. Works with OUT parameters, too. Only the basic form declared with RETURNS *type* requires an explicit RETURN *value*;. – Lucio

Answer 2

I'm unsure if you can mix the two (try it…).

That said, it'll be much more efficient in you use-case to write a single query with a union all clauses that returns all rows directly:

return query
select 'mystatus' as paramName, status::varchar as value
union all
select 'mymessage' as paramName, message as value
union all
SELECT 'mycolumnname1' as paramName,mycolumn1 as value
FROM tb1
WHERE column1 = val
union all
…

You might also find this contrib module helpful, btw:

http://www.postgresql.org/docs/current/static/tablefunc.html

And, if possible, revisit your schema or the way you use it, so you don't need this kind of function to begin with — set returning functions that return potentially large sets can be particularly inefficient. In your case, it seems like you want three columns. Why not simple use?

select col1, col2, col3 from tbl

Fivefinger

Hi @Denis, I am testing Postgres for a possible migration for one of ours products, so changing that kind of things is not possible. Status and message are variables inside the same function but obtained in different situations so can not be used in the same query. That being said, how can I attach different queries results with same field types in a same function resultset. Can be used return query or other keyword multiple times for that? – Deepfreeze

Methinks try it as you wrote it, and if PG complains about mixing return next and return query, or multiple return query, use return next all over. Return query is faster insofar as I can recollect, btw, so if you can change that last one to use return query instead of return next, it's good too. – Fivefinger

Answer 3

For example, you can use a RETURN NEXT and RETURN QUERY statement together in my_func() which returns SETOF RECORD type as shown below:

CREATE FUNCTION my_func() RETURNS SETOF RECORD AS $$
DECLARE 
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT row;
  END LOOP;

  RETURN QUERY VALUES ('Robert','Wilson'), ('Mark','Taylor');
END;
$$ LANGUAGE plpgsql;

*Memos:

• A RETURN NEXT and RETURN QUERY statement cannot exit a function while a RETURN statement can.

• You can also use multiple RETURN NEXT or RETURN QUERY statements in my_func() as I explain it in my answer.

Then, calling my_func() returns 4 rows as shown below:

postgres=# SELECT * FROM my_func() AS (first_name TEXT, last_name TEXT);
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
 Robert     | Wilson
 Mark       | Taylor
(4 rows)

And, you can use a RETURN NEXT and RETURN QUERY statement in my_func() which returns TABLE() type as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(first_name TEXT, last_name TEXT) AS $$
BEGIN
  FOR first_name, last_name IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT; -- Here
  END LOOP;
  RETURN QUERY VALUES ('Robert','Wilson'), ('Mark','Taylor'); -- Here
END;
$$ LANGUAGE plpgsql;

Then, calling my_func() returns 4 rows as shown below:

postgres=# SELECT * FROM my_func();
 first_name | last_name
------------+-----------
 John       | Smith
 David      | Miller
 Robert     | Wilson
 Mark       | Taylor
(4 rows)

postgres=# SELECT my_func();
     my_func
-----------------
 (John,Smith)
 (David,Miller)
 (Robert,Wilson)
 (Mark,Taylor)
(4 rows)

Crocus