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

Поиск
Список
Период
Сортировка
От Sigurður Reynisson
Тема Newbie Q:"RETURN cannot have a parameter in function returning set"?
Дата
Msg-id 356418900506211304a4ed480@mail.gmail.com
обсуждение исходный текст
Ответы Re: Newbie Q:"RETURN cannot have a parameter in function returning set"?  (Michael Fuhr <mike@fuhr.org>)
Список pgsql-novice
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 ===

В списке pgsql-novice по дате отправления:

Предыдущее
От: George McQuade
Дата:
Сообщение: Subquery
Следующее
От: Keith Worthington
Дата:
Сообщение: Re: Subquery