0

I am trying to run the following function and I get the error "syntax error at or near "$1" Could someone help me out fix this. Thanks!

CREATE OR REPLACE FUNCTION vin_calc() RETURNS text AS
$BODY$
DECLARE
    r res%rowtype;
    i integer default 0;
    x  text;
    curs2 CURSOR FOR SELECT * FROM res;
BEGIN
    FOR r IN curs2 

    LOOP
    if(x!=r.prod_grp_nm)
    i=0 
    end

     i:=i+1;

     if (i=1)
      update res set duration =0 where CURRENT OF curs2;

     elsif(i=2)
     update res set duration =1 where CURRENT OF curs2;

     elsif(i>=3)
     update res set gwma_duration =0.75*lag(res.duration,1)+(0.125*r.dur) where CURRENT OF curs2;
     end    

    x:=r.prod_grp_nm;      

    END LOOP;
    RETURN 'yes';
END
$BODY$
LANGUAGE 'plpgsql' ;
Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
user2569524
  • 1,651
  • 7
  • 32
  • 57
  • See [Conditionals](http://www.postgresql.org/docs/9.1/static/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS) in the plpgsql manual and notice the lack of `THEN` and `END IF` in your code does not conform to the valid forms. – Daniel Vérité Feb 10 '14 at 16:08
  • Thanks for the catch.I am trying to use CURRENT OF curs2 to fetch the current row under the loop ?it doesnt work gives me an error ERROR: syntax error at or near "$1" LINE 1: ...res set gwma_duration =0 where CURRENT OF $1 . could you please help – user2569524 Feb 10 '14 at 16:13
  • Can you add the result of `select version();` to your question? – Daniel Vérité Feb 10 '14 at 16:26
  • its "PostgreSQL 8.2.15 (Greenplum Database 4.2.3.2 build 1) – user2569524 Feb 10 '14 at 16:27
  • [Link to my other question](http://stackoverflow.com/questions/21682574/get-column-value-of-previous-row-in-postgres) – user2569524 Feb 10 '14 at 16:28
  • So it's not postgresql, it's greenplum. It's based on postgresql but is a different, incompatible product. You want to refer to greenplum's documentation to check if it supports `WHERE CURRENT OF` in an update. – Daniel Vérité Feb 10 '14 at 16:43
  • @user2569524 If you're using some incompatible PostgreSQL fork, **always say so**, don't just pretend it's PostgreSQL. – Craig Ringer Feb 11 '14 at 00:00

0 Answers0