Thread: Newbie Q:"RETURN cannot have a parameter in function returning set"?

Just did a LAPP install, FC4, A2.0.54, PSQL8.0.3, PHP5.0.4 and I'm using
pgAdmin III on my WinXP desktop.

Ported a database from a system running PostgreSQL 7.4.2 and after a few basic
tweaks I'm getting the error below. I've done a little RTFM and
Googling but so far
no results. Any pointers on the error or where to RTFM it welcome,
thanks in advance!

=== Error msg ===
Warning: pg_query() [function.pg-query]: Query failed: ERROR: RETURN
cannot have a parameter in function returning set; use RETURN NEXT at
or near "I" at character 550 QUERY: DECLARE I RECORD; -- USE_T
phl_usr_dims_view%ROWTYPE; LNG_cd ALIAS FOR $4; NEW_LNG_VALUE
VARCHAR(50); BEGIN for I IN SELECT * FROM PHL_USR_DIMS_VIEW WHERE
USR_ID = $1 AND (ENTRY_DT >= $2 AND ENTRY_DT <= $3) ORDER BY USR_ID,
ENTRY_DT, PARAM_ID LOOP SELECT phl_GET_LNG_VAL(LNG_CD, I.PARAM_NAME )
INTO NEW_LNG_VALUE; if (new_lng_value is not null) and (new_lng_value
<> i.param_name) then I.PARAM_NAME := NEW_LNG_VALUE; I.LNG_INDEX := 1;
else I.LNG_INDEX := 0; END IF; RETURN NEXT I; END LOOP; RETURN I; END;
CONTEXT: compile of PL/pgSQL function "phl_get_usr_params" near line
18 in /usr/local/apache/htdocs/dbConnection.php on line 45

=== Function Code ===
-- Function: phl_get_usr_params(int4, date, date, int4)

-- DROP FUNCTION phl_get_usr_params(int4, date, date, int4);

CREATE OR REPLACE FUNCTION phl_get_usr_params(int4, date, date, int4)
  RETURNS SETOF phl_usr_dims_view AS
$BODY$
DECLARE
 I RECORD;
-- USE_T phl_usr_dims_view%ROWTYPE;
 LNG_cd ALIAS FOR $4;
 NEW_LNG_VALUE VARCHAR(50);
BEGIN

 for I IN SELECT * FROM PHL_USR_DIMS_VIEW WHERE USR_ID = $1 AND
(ENTRY_DT >= $2 AND ENTRY_DT <= $3) ORDER BY USR_ID, ENTRY_DT,
PARAM_ID LOOP
  SELECT phl_GET_LNG_VAL(LNG_CD, I.PARAM_NAME ) INTO NEW_LNG_VALUE;
  if (new_lng_value is not null) and (new_lng_value <> i.param_name) then
    I.PARAM_NAME := NEW_LNG_VALUE;
    I.LNG_INDEX := 1;
   else
    I.LNG_INDEX := 0;
  END IF;
  RETURN NEXT I;
 END LOOP;
 RETURN I;
END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION phl_get_usr_params(int4, date, date, int4) OWNER TO postgres;
=== End of post ===

On Tue, Jun 21, 2005 at 08:04:07PM +0000, Sigurður Reynisson wrote:
>
> Warning: pg_query() [function.pg-query]: Query failed: ERROR: RETURN
> cannot have a parameter in function returning set; use RETURN NEXT at
> or near "I" at character 550
...
>  END LOOP;
>  RETURN I;
> END;$BODY$

See "Returning From a Function" in the PL/pgSQL documentation:

http://www.postgresql.org/docs/8.0/static/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

"When a PL/pgSQL function is declared to return SETOF sometype, the
procedure to follow is slightly different.  In that case, the
individual items to return are specified in RETURN NEXT commands,
and then a final RETURN command with no argument is used to indicate
that the function has finished executing."

Change that last "RETURN I" to "RETURN".  PostgreSQL 8 is pickier
about PL/pgSQL syntax than previous versions.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/