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

by

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.

More Related Contents:

  1. What are ‘$$’ used for in PL/pgSQL
  2. INSERT with dynamic table name in trigger function
  3. The forgotten assignment operator “=” and the commonplace “:=”
  4. Truncating all tables in a Postgres database
  5. PostgreSQL: ERROR: 42601: a column definition list is required for functions returning “record”
  6. How to set value of composite variable field using dynamic SQL
  7. How do I do large non-blocking updates in PostgreSQL?
  8. Execute a dynamic crosstab query
  9. Is there a way to disable function overloading in Postgres
  10. Can I make a plpgsql function return an integer without using a variable?
  11. PostgreSQL function returning multiple result sets
  12. Dynamic SQL (EXECUTE) as condition for IF statement
  13. Returning from a function with OUT parameter
  14. SELECT raises exception in PL/pgSQL function
  15. Test for null in function with varying parameters
  16. Return SETOF rows from PostgreSQL function
  17. Error when setting n_distinct using a plpgsql variable
  18. Postgres FOR LOOP
  19. Dynamically generate columns for crosstab in PostgreSQL
  20. Function to loop through and select data from multiple tables
  21. PL/pgSQL functions: How to return a normal table with multiple columns using an execute statement
  22. SQL Sub queries in check constraint
  23. Cursor based records in PostgreSQL
  24. ERROR: unterminated quoted string at or near
  25. How can I send some http request from postgresql function or trigger
  26. Select from a table variable
  27. Hashing a String to a Numeric Value in PostgreSQL
  28. Loop on tables with PL/pgSQL in Postgres 9.0+
  29. Loop through columns of RECORD
  30. Format specifier for integer variables in format() for EXECUTE?

Leave a Comment