Thread: PL/pgSQL function syntax question?

<div dir="ltr">i get the following error when i try and create the following function:<br /><br />Basically, i am
tryingto have two different dates compared and only the most recent returned to me.  This seems pretty straight
forward,what I am doing wrong here?<br /><br /><span style="color: rgb(51, 51, 255);">create or replace function
fcn_pick_date(v_dtdate) <br />returns date as $$  <br />  DECLARE<br />      v_dt date; <br />  BEGIN<br />    for
v_recordin select last_periodic, last_boot<br />         from mediaportal<br />    loop<br />    if v_dt >=
v_record.last_bootthen<br />       v_dt := v_record.last_periodic;<br />    else<br />        v_dt :=
v_record.last_boot;<br/>    end if;<br />    end loop;<br />    return (v_dt);<br /> END;<br />$$ LANGUAGE
'plpgsql';</span><br/><br /><br /><span style="color: rgb(204, 0, 0);">ERROR:  loop variable of loop over rows must be
recordor row variable at or near "loop" at character 195<br /><br />********** Error **********<br /><br />ERROR: loop
variableof loop over rows must be record or row variable at or near "loop"<br />SQL state: 42601<br />Character: 195<br
/></span><br/><br /></div> 

-------------- Original message ----------------------
From: "Ruben Gouveia" <rubes7202@gmail.com>
> 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
aklaver@comcast.net

--- On Mon, 9/8/08, Ruben Gouveia <rubes7202@gmail.com> wrote:

> From: Ruben Gouveia <rubes7202@gmail.com>
> Subject: [SQL] PL/pgSQL function syntax question?
> To: "pgsql-sql" <pgsql-sql@postgresql.org>
> Date: Monday, September 8, 2008, 9:40 PM
> 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

where you declare v_record?

i think you have declare v_record to record or var array