ERROR: cannot use RETURN NEXT in a non-SETOF function

Question

I have two tables with date-columns (year, month, day and a combination of the three called "stamp") and associated values (avalue). I need my function to return the ratio between two tables (at a specified date), return a fixed value after a previously-specified limit-date, and if the date is not available in the data (but lower than the limit), it should choose the first available date following the input.

Here's the code I wrote:

CREATE OR REPLACE FUNCTION myfunction(theyear int, themonth int, theday int) RETURNS real AS '
DECLARE
  foo tablenamea%rowtype;
BEGIN
  IF ((theyear >= 2000) AND (themonth >= 6)) OR (theyear > 2000) THEN
    RETURN 0.1;
  ELSE 
    FOR foo IN SELECT (a.avalue/b.avalue) FROM tablenamea AS a, tablenameb AS b
      WHERE a.stamp = b.stamp AND a.year = theyear AND a.month = themonth AND a.day >= theday ORDER BY a.year, a.month, a.day
    LOOP
      RETURN NEXT foo;
    END LOOP;
    RETURN;
  END IF;
END;
' LANGUAGE plpgsql;

This keeps giving me this error:

ERROR: cannot use RETURN NEXT in a non-SETOF function

Kelleykelli

Answer 1

It's telling you that you should return a real, instead of foo (which is a row).

Probably return foo.somefield instead.

Also, add a limit 1 instead of a for loop, since I presume you're only really interested in the first row. If not, declare it as returning e.g. table (ratio real) and use return query.

Badtempered

Thank you. I'm really sorry to ask, but I'm very new to PL/pgSQL and I'm having some difficulty implementing your suggestions. Do you think you could possibly post what you mean in code? – Kelleykelli

Answer 2

I got the same error below:

ERROR: cannot use RETURN NEXT in a non-SETOF function

When I tried to create my_func() which has RECORD type in a RETURNS clause and RETURN NEXT statement as shown below:

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

*Memos:

• I also got and solved the same kind of error with a RETURN QUERY statement.

So, I set SETOF RECORD type to the RETURNS clause as shown below, then I could create my_func() without error:

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

Then, I could call my_func() in a FROM clause without error as shown below:

postgres=# SELECT * FROM my_func() AS (f_n TEXT, l_n TEXT);
  f_n  |  l_n
-------+--------
 John  | Smith
 David | Miller
(2 rows)

Or, I set TABLE() type to the RETURNS clause, then I set f_n and l_n parameter to the FOR statement instead of row local variable as shown below. *If you set row local variable to the RETURN NEXT statement, there is the error:

                               -- ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓ ↓
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 */;
  END LOOP;
END;    
$$ LANGUAGE plpgsql;

Then, I could call my_func() without error as shown below:

postgres=# SELECT * FROM my_func();
  f_n  |  l_n
-------+--------
 John  | Smith
 David | Miller
(2 rows)

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

Davison