Re: Syntax error for Function

[Sachin Srivastava <ssr.teleatlas@xxxxxxxxx>]

Dear Adiran,

Thanks for your help !!

First I want to say it's not giving the error for this ""languagetype@xxxxxxxxxxxxxxxxxxxxx", so there is any need to do the change as suggested by you.

Second you suggested " exit with cur1; " - You are right after putting the semi column my code is working fine.

There is also one query I have changed this line    "langCursor cur1%rowtype;" as below:

langCursor RECORD; --cur1%rowtype;

Please read my code once again and suggest I did correct these change or not because this is suggested by one of my friend and first I am getting the error for this line.

I am the new one for plsql code that's why I am taking the expert advice.

Thanks

SS

On Wed, Jan 20, 2016 at 8:30 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

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

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@xxxxxxxxxxxxxxxxxxxxx>";
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@xxxxxxxxx

<mailto:thom@xxxxxxxxx>> wrote:

    On 20 January 2016 at 12:15, Sachin Srivastava

    <ssr.teleatlas@xxxxxxxxx <mailto:ssr.teleatlas@xxxxxxxxx>> 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@xxxxxxxxxxx