I see v_dt is not initialized before comparison, there should be some value in it before it is useful.
--Imad On Tue, Sep 9, 2008 at 8:47 AM, Adrian Klaver <[EMAIL PROTECTED]> wrote: > > -------------- Original message ---------------------- > From: "Ruben Gouveia" <[EMAIL PROTECTED]> > > i get the following error when i try and create the following function: > > > > Basically, i am trying to have two different dates compared and only the > > most recent returned to me. This seems pretty straight forward, what I > am > > doing wrong here? > > > > create or replace function fcn_pick_date(v_dt date) > > returns date as $$ > > DECLARE > > v_dt date; > > BEGIN > > for v_record in select last_periodic, last_boot > > from mediaportal > > loop > > if v_dt >= v_record.last_boot then > > v_dt := v_record.last_periodic; > > else > > v_dt := v_record.last_boot; > > end if; > > end loop; > > return (v_dt); > > END; > > $$ LANGUAGE 'plpgsql'; > > > > > > ERROR: loop variable of loop over rows must be record or row variable at > or > > near "loop" at character 195 > > > > ********** Error ********** > > > > ERROR: loop variable of loop over rows must be record or row variable at > or > > near "loop" > > SQL state: 42601 > > Character: 195 > > You need to DECLARE v_record as a RECORD variable. > v_record RECORD; > > -- > Adrian Klaver > [EMAIL PROTECTED] > > > > > > ---------- Forwarded message ---------- > From: "Ruben Gouveia" <[EMAIL PROTECTED]> > To: pgsql-sql <pgsql-sql@postgresql.org> > Date: Mon, 8 Sep 2008 21:41:14 +0000 > Subject: [SQL] PL/pgSQL function syntax question? > i get the following error when i try and create the following function: > > Basically, i am trying to have two different dates compared and only the > most recent returned to me. This seems pretty straight forward, what I am > doing wrong here? > > create or replace function fcn_pick_date(v_dt date) > returns date as $$ > DECLARE > v_dt date; > BEGIN > for v_record in select last_periodic, last_boot > from mediaportal > loop > if v_dt >= v_record.last_boot then > v_dt := v_record.last_periodic; > else > v_dt := v_record.last_boot; > end if; > end loop; > return (v_dt); > END; > $$ LANGUAGE 'plpgsql'; > > > ERROR: loop variable of loop over rows must be record or row variable at > or near "loop" at character 195 > > ********** Error ********** > > ERROR: loop variable of loop over rows must be record or row variable at or > near "loop" > SQL state: 42601 > Character: 195 > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > >
