Re: PL/pgSQL function syntax question?

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





---------- Forwarded message ----------
From: "Ruben Gouveia" <rubes7202@gmail.com>
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