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 ===
