Re: Syntax error for Function

Dear Adiran,

Thanks for your help !!

First I want to say it's not giving the error for this ""
languagetype(at)repos(dot)birchstreet(dot)net", 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(dot)klaver(at)aklaver(dot)com>
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(at)repos(dot)birchstreet(dot)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(at)repos(dot)birchstreet(dot)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(at)linux(dot)com
>> <mailto:thom(at)linux(dot)com>> wrote:
>>
>> On 20 January 2016 at 12:15, Sachin Srivastava
>> <ssr(dot)teleatlas(at)gmail(dot)com <mailto:ssr(dot)teleatlas(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
>