hi may i know y am i getting this error - Mailing list pgsql-sql

Hi

When I am executing my function its giving error.. first time its running properly.. but when I am executing second time I am getting error

create or replace function irla_punching_medium_insert1(in_proj_code numeric,in_pt_code varchar,in_mon_abs date) returns varchar as'

declare

l_p varchar;

credit_amount numeric;

debit_amount numeric;

l_vr_no varchar;

l_count integer;

begin

select irla_code into  l_p from npo_project_irla_code where npo_proj_code=$1 and pt_code=$2;

create table dummy_table without oids as

select side,irla_code,substr(type_charge,1,1)||substr(type_charge,2,1) || substr(type_charge,4,1) ||substr(type_charge,5,1) as type_charge,npo_code from irla_npo_pm_codes_master

where type_charge=''(+-)CR''  or type_charge=''(+-)RC''

union all

select side,irla_code,substr(type_charge,1,1)||substr(type_charge,3,1)||substr(type_charge,4,1) || substr(type_charge,6,1) as type_charge,npo_code from irla_npo_pm_codes_master

where type_charge=''(+-)CR''  or type_charge=''(+-)RC''

union all select side,irla_code,type_charge,npo_code from irla_npo_pm_codes_master where type_charge=''(+)C''  or type_charge=''(+)R'' ;

update dummy_table set irla_code=l_p where irla_code=''P'';

create table amount_table without oids as select dt.side,dt.irla_code,sum(npoaa.amount) as amount,dt.type_charge from npo_abs_amount npoaa,dummy_table dt where dt.side=npoaa.side and dt.npo_code=npoaa.npo_code

 and extract(month from to_date(npoaa.mon_abs,''yyyy-mm-dd''))||''/''|| extract(year from to_date(npoaa.mon_abs,''yyyy-mm-dd''))

=extract(month from to_date($1,''yyyy-mm-dd''))||''/''|| extract(year from to_date($1,''yyyy-mm-dd''))  and npoaa.proj_code=$1 and npoaa.pt_code= $2 group by dt.irla_code ,dt.type_charge,dt.side ;

drop table dummy_table;

select cast(substr(max(vr_no),1,2) || cast(substr(max(vr_no),3,1) as integer)+1 as varchar) into l_vr_no from irla_punchingmedium_top_amt ;

select sum(amount) into credit_amount from amount_table where side=''C'';

select sum(amount) into debit_amount from amount_table where side=''D'';

insert into irla_punchingmedium_top_amt (vr_no,credit_tot_amt,class_of_vr,mon_abs,proj_code,pt_code,debit_tot_amt)values

(l_vr_no,credit_amount,1,$3,$1,$2,debit_amount);

insert into irla_punchingmedium_amt (amount,codehead,type_of_charge,vr_no)  select amount,irla_code,type_charge,(select l_vr_no) from amount_table;

raise notice''%'', l_vr_no;

drop table amount_table;

 raise notice ''%2'', l_count;

return ''inserted'';

end;

'language'plpgsql';

            ERROR:  relation with OID 75275 does not exist

CONTEXT:  SQL statement "update dummy_table set irla_code= $1  where irla_code='P'"

PL/pgSQL function "irla_punching_medium_insert1" line 19 at SQL statement

Thanks  &  Regards

Penchal reddy | Software Engineer           

Infinite Computer Solutions | Exciting Times…Infinite Possibilities...

SEI-CMMI level 5 | ISO 9001:2000

IT SERVICES | BPO                                                                                                                                                                          

Telecom | Finance | Healthcare | Manufacturing | Energy & Utilities | Retail & Distribution | Government                                                       

Tel +91-80-5193-0000(Ext:503)| Fax  +91-80-51930009 | Cell No  +91-9980012376|www.infics.com          

Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and/ or its Customers and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at info.in@infics.com and delete this mail from your records.