Re: Syntax error for Function

On 01/20/2016 08:00 AM, Sachin Srivastava wrote:
> 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
> <mailto:languagetype(at)repos(dot)birchstreet(dot)net>", so there is any need to do
> the change as suggested by you.

You are not seeing a syntax error, but I am pretty sure you will see a
run error as from what I gather languagetype(at)repos(dot)(dot) is an Oracle construct:

http://stackoverflow.com/questions/296263/what-is-the-meaning-of-symbol-in-oracle-sql

Pretty sure Postgres is going to fail on:

cur1 CURSOR FOR SELECT distinct(language_id) from
"languagetype(at)repos(dot)birchstreet(dot)net "

when it tries to execute the query.

>
> 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;

No ; at end of comment:

http://www.postgresql.org/docs/9.4/static/plpgsql-structure.html

>
> 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.

That is why I suggested taking a look at the plpgsql section of the
manual. A quick run through will answer most of your questions.

>
>
> Thanks
> SS
>
> On Wed, Jan 20, 2016 at 8:30 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto: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
> <mailto: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
> <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>
> <mailto: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>
> <mailto: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 <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com