Re: Syntax error for Function - Mailing list pgsql-general
| From | Sachin Srivastava |
|---|---|
| Subject | Re: Syntax error for Function |
| Date | |
| Msg-id | CAFzqEhJw2JYhjxwMGZB0ENPD4inLxRj-a4jTwnhMSUsRu4uxog@mail.gmail.com Whole thread Raw |
| In response to | Re: Syntax error for Function (Thom Brown <thom@linux.com>) |
| Responses |
Re: Syntax error for Function
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | 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
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
--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;
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_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
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;
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;
$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:
Well, it says that the problem occurs on line 44, so what's on theOn 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;
>
> ------------------------
previous lines it's receiving? Are you sending an unterminated query
prior to that?
Thom
pgsql-general by date: