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

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