Skip site navigation (1) Skip section navigation (2)

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

From: SigurĂ°ur Reynisson <siggir(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Newbie Q:"RETURN cannot have a parameter in function returning set"?
Date: 2005-06-21 20:04:07
Message-ID: 356418900506211304a4ed480@mail.gmail.com (view raw or flat)
Thread:
Lists: 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 ===

Responses

pgsql-novice by date

Next:From: Keith WorthingtonDate: 2005-06-21 20:34:33
Subject: Re: Subquery
Previous:From: George McQuadeDate: 2005-06-21 19:37:32
Subject: Subquery

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group