plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function

Question

I have a plpgsql function in PostgreSQL 9.2 which returns a table. The function runs several SELECTs that return the same columns as the function and then either returns those results or raises an exception, depending on some checks. The only way I can see of doing this is with FOR ... LOOP, but I can't figure out a convenient way of returning the row.

I want to do something like this:

CREATE OR REPLACE FUNCTION my_function()
RETURNS TABLE(column1 integer, column2 boolean, ...)
AS $BODY$
DECLARE
    result_row RECORD;
BEGIN
    FOR result_row IN (SELECT * FROM other_function_returning_same_columns()) LOOP
        IF something_wrong_with(result_row) THEN
            RAISE EXCEPTION 'Something went wrong';
        END IF;

        RETURN NEXT result_row;
    END LOOP;
END
$BODY$ LANGUAGE plpgsql STABLE;

This gives me an error:

ERROR: RETURN NEXT cannot have a parameter in function with OUT parameters

I'm not sure why Postgres is complaining here, because my code looks a lot like the example in the documentation, except that my function returns TABLE instead of SETOF. There are no OUT parameters.

I eventually managed to get it to work using

RETURN QUERY SELECT result_row.column1, result_row.column2, ...;

but having to list all the columns all the time is ugly and harder to maintain. I'm sure there must be a better way.

Do you need an exception thrown, or do you just want qualifying rows back? — Erwin Brandstetter

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2012-12-26T11:19:54

RETURN NEXT just returns what output parameters currently hold. The manual:

If you declared the function with output parameters, write just RETURN NEXT with no expression.

You object:

There are no OUT parameters.

Output parameters are declared among function parameters with the keyword OUT or INOUT, or implicitly in your RETURNS clause:

RETURNS TABLE(column1 integer, column2 boolean, ...)

Here, column1 and column2 are OUT parameters, too.

This should do it:

CREATE OR REPLACE FUNCTION my_function()
  RETURNS TABLE(column1 integer, column2 boolean, ...)
  LANGUAGE plpgsql STABLE AS
$func$
BEGIN
   FOR column1, column2, ... IN 
      SELECT * FROM other_function_returning_same_columns()
   LOOP
      IF something_wrong_with(column1, column2, ...) THEN
         RAISE EXCEPTION 'Something went wrong';
      END IF;

      RETURN NEXT;
   END LOOP;
END
$func$;

Simpler with a registered type

You can further simplify with a registered composite type:

CREATE TYPE mytype (column1 integer, column2 boolean, ...);

Or, if your type happens to match a table definition, you already have that type, because every table name can be used as type name in PostgreSQL. Then simplify:

CREATE OR REPLACE FUNCTION my_function()
  RETURNS SETOF mytype
  LANGUAGE plpgsql STABLE AS
$func$
DECLARE
   _r mytype;
BEGIN
   FOR _r IN 
     SELECT * FROM other_function_returning_same_columns()
   LOOP
      IF something_wrong_with(_r) THEN
         RAISE EXCEPTION 'Something went wrong';
      END IF;

      RETURN NEXT _r;
   END LOOP;
END
$func$;

Reorganize!

If you integrate the RAISE command into your helper function something_wrong_with(), invert the logic and more conveniently name it everything_groovy(), then you can completely replace my_function() with this simple query:

SELECT *
FROM   other_function_returning_same_columns() f
WHERE  everything_groovy(f);

Or integrate the RAISE into the base function other_function_returning_same_columns() to further simplify (and make it faster). If you only want to RAISE EXCEPTION in certain situations, you can always add a parameter (with a default) to switch it on / off.

plpgsql error "RETURN NEXT cannot have a parameter in function with OUT parameters" in table-returning function

1 Answers

Simpler with a registered type

Reorganize!