21

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.

Improve this question
2
  • Do you need an exception thrown, or do you just want qualifying rows back? Commented Dec 26, 2012 at 11:36
  • Yes, I do want an exception thrown in some cases. Commented Dec 27, 2012 at 3:08

2 Answers 2

Reset to default
34

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks. Looks like I have to list all the columns whatever I do, but this is less code than what I had before.
@EM: Well, there are always more options. I added alternatives to my answer.
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.)
0

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)
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.