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.

Answer 1

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.

Answer 2

I got the same error below:

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

When I tried to create my_func() which has the RETURNS TABLE() with f_n and l_n parameter and the RETURN NEXT statement with row as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(f_n TEXT, l_n TEXT) AS $$
DECLARE                -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ Here ↑ ↑ ↑ ↑ ↑ ↑ ↑ 
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT row; -- Here
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

So, I removed row local variable from RETURN NEXT as shown below, then I could create my_func() without error:

CREATE FUNCTION my_func() RETURNS TABLE(f_n TEXT, l_n TEXT) AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT /* row */; -- Here
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

But, calling my_func() returned empty rows as shown below:

postgres=# SELECT my_func();
 my_func
---------
 (,)
 (,)
(2 rows)

So, I set f_n and l_n parameter to the FOR statement instead of row local variable as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(f_n TEXT, l_n TEXT) AS $$
-- DECLARE
  -- row RECORD;
BEGIN
  FOR f_n, l_n IN VALUES ('John','Smith'), ('David','Miller') LOOP
    RETURN NEXT /* row */; -- Here
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

Then, calling my_func() returned expected non-empty rows as shown below:

postgres=# SELECT my_func();
    my_func
----------------
 (John,Smith)
 (David,Miller)
(2 rows)

Be careful, if you assign row local variable to f_n and l_n parameter in the FOR statement as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(f_n TEXT, l_n TEXT) AS $$
DECLARE
  row RECORD;
BEGIN
  FOR row IN VALUES ('John','Smith'), ('David','Miller') LOOP
    f_n := row;
    l_n := row;
    RETURN NEXT /* row */; -- Here
  END LOOP;
  RETURN;
END;    
$$ LANGUAGE plpgsql;

Then, calling my_func() returns unexpected non-empty rows as shown below:

postgres=# SELECT my_func();
               my_func
-------------------------------------
 ("(John,Smith)","(John,Smith)")
 ("(David,Miller)","(David,Miller)")
(2 rows)

In addition, you can create my_func() which has RETURNS TABLE() and RETURN QUERY statement without error as shown below:

CREATE FUNCTION my_func() RETURNS TABLE(f_n TEXT, l_n TEXT) AS $$
BEGIN                  -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ Here ↑ ↑ ↑ ↑ ↑ ↑ ↑
  RETURN QUERY VALUES ('John','Smith'), ('David','Miller');
END; -- ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ Here ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
$$ LANGUAGE plpgsql;

Then, calling my_func() returned expected non-empty rows as shown below:

postgres=# SELECT my_func();
    my_func
----------------
 (John,Smith)
 (David,Miller)
(2 rows)