Dear Thom, Please find the complete code as below and suggest now.
---------------------- -- 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 " languaget...@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 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 <t...@linux.com> wrote: > On 20 January 2016 at 12:15, Sachin Srivastava <ssr.teleat...@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 >