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

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@repos.birchstreet.net
> <mailto:languagetype@repos.birchstreet.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@repos.. 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@repos.birchstreet.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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@repos.birchstreet.net
>         <mailto: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
>         <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>
>         <mailto: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>
>         <mailto: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 <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com