Re: Syntax error for Function - Mailing list pgsql-general

On 01/20/2016 06:32 AM, Sachin Srivastava wrote:
> Dear Thom,
>
> Please find the complete code as below and suggest now.

I would suggest spending some time here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql.html

in particular:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-control-structures.html#PLPGSQL-CONTROL-STRUCTURES-LOOPS

and here:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-porting.html

Comments in-line

>
> ----------------------
>
> -- Function: gen_budget_for_next_year(bigint, bigint, bigint)
> -- DROP FUNCTION gen_budget_for_next_year(bigint, bigint, bigint);
> CREATE OR REPLACE FUNCTION gen_budget_for_next_year(
>      subid bigint,
>      compid bigint,
>      formonth bigint)
>    RETURNS void AS
> $BODY$
> DECLARE
> --Version:
> --2015.01 PM T15422-10- Generate budget period for coming years. 05/01/2015
> cur1 CURSOR FOR SELECT distinct(language_id) from
> "languagetype@repos.birchstreet.net

Have you tried the above. I know quoting it got you pass the syntax
error, but I am pretty sure it not going to do what it did in Oracle.

> <mailto:languagetype@repos.birchstreet.net>";
> sFound bigint := 0;
> recCount bigint :=0;
> period varchar(100);
> firstDate varchar(100);
> lastDate varchar(100);
> curMonth varchar(100);
> langCursor RECORD; --cur1%rowtype;
>
> BEGIN
>    loop
>    open cur1;
>    IF NOT FOUND THEN EXIT; END IF; -- apply on recCount >= forMonth;
>          select to_char(TO_DATE(LOCALTIMESTAMP) + recCount*'1
> month'::interval,'YYYYMM') into period  ;
>          select to_date(period||'01','YYYYMMDD') into firstDate  ;
>          select TO_DATE(LOCALTIMESTAMP) + recCount*'1 month'::interval
> into lastDate  ;
>          select to_char(TO_DATE(LOCALTIMESTAMP) + recCount*'1
> month'::interval, 'YYYY MM MONTH') into curMonth ;
>          recCount :=recCount+1;
>    loop
>    fetch cur1 into langCursor;
>    exit when cur1

 From loop link above, this needs to be

exit when cur1;

>              select Count(0) into sFound  from budget_period t where
> t.subscriber_id =subID
>              and t.period_number = period and
> t.language_id=langCursor.Language_Id;
>              if(sFound = 0)then
>                  insert into budget_period (subscriber_id, company_id,
> period_number, period_name,
>                  period_length_code, first_day, last_day,creation_date,
> creation_user, update_date, update_user, language_id)
>                  values(subID, compID, period,  curMonth,  'MONTH',
>                  firstDate, lastDate, LOCALTIMESTAMP,
> 'Admin',LOCALTIMESTAMP, 'Admin', langCursor.Language_Id);
>              end if;
>    end loop;
>    close cur1;
>    end loop;
>
> commit;
> END;
> $BODY$
>    LANGUAGE plpgsql VOLATILE SECURITY DEFINER
>    COST 100;
> ALTER FUNCTION gen_budget_for_next_year(bigint, bigint, bigint)
>    OWNER TO postgres;
>
> --------------------------------
>
> On Wed, Jan 20, 2016 at 6:05 PM, Thom Brown <thom@linux.com
> <mailto:thom@linux.com>> wrote:
>
>     On 20 January 2016 at 12:15, Sachin Srivastava
>     <ssr.teleatlas@gmail.com <mailto:ssr.teleatlas@gmail.com>> wrote:
>      > I am unable to find out the syntax error in below code, please
>     suggest?
>      >
>      >
>      >
>      > ERROR:  syntax error at or near "select"
>      > LINE 44:             select Count(0) into sFound  from
>     budget_period ...
>      >                      ^
>      > ********** Error **********
>      > ERROR: syntax error at or near "select"
>      > SQL state: 42601
>      > Character: 1190
>      >
>      > Code as below:
>      > -------------------------
>      >
>      > select Count(0) into sFound  from budget_period t where
>     t.subscriber_id
>      > =subID
>      >             and t.period_number = period and
>      > t.language_id=langCursor.Language_Id;
>      >             if(sFound = 0)then
>      >                 insert into budget_period (subscriber_id, company_id,
>      > period_number, period_name,
>      >                 period_length_code, first_day,
>     last_day,creation_date,
>      > creation_user, update_date, update_user, language_id)
>      >                 values(subID, compID, period,  curMonth,  'MONTH',
>      >                 firstDate, lastDate, LOCALTIMESTAMP,
>     'Admin',LOCALTIMESTAMP,
>      > 'Admin', langCursor.Language_Id);
>      >             end if;
>      >
>      > ------------------------
>
>     Well, it says that the problem occurs on line 44, so what's on the
>     previous lines it's receiving?  Are you sending an unterminated query
>     prior to that?
>
>     Thom
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com