Syntax error for Function

On 20 January 2016 at 12:15, Sachin Srivastava <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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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>";
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>> wrote:

On 20 January 2016 at 12:15, Sachin Srivastava
<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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dear Adiran,

Thanks for your help !!

First I want to say it's not giving the error for this ""
languagetype@repos.birchstreet.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.klaver@aklaver.com>
wrote:

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general