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, Dec 26 '12 at 11:36

Erwin Brandstetter · Accepted Answer · 2020-06-04T14:41:57.720

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.

Thanks. Looks like I have to list all the columns whatever I do, but this is less code than what I had before. — EM0, Dec 27 '12 at 03:17
@EM: Well, there are always more options. I added alternatives to my answer. — Erwin Brandstetter, Dec 27 '12 at 05:01
Thanks again! Putting the check into another function is an interesting idea. It won't really help in this case, because the real code is more complex than my example, but there may be other functions where I can use this trick instead of looping over results. (It would work here, but would be more complex than listing the columns.) — EM0, Dec 27 '12 at 11:46

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

1 Answers1

Simpler with a registered type

Reorganize!

Linked