PostgreSQL SQL - hi may i know y am i getting this error

Author

hi may i know y am i getting this error

Penchalaiah P.

2006-11-17, 5:24 am

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_cod
e
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_cod
e where
npo_proj_code=$1 and pt_code=$2;

create table dummy_table without oids as

select side,irla_code,subst
r(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_ma
ster

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

union all

select
side,irla_code,subst
r(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_ma
ster

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

union all select side,irla_code,type_
charge,npo_code from
irla_npo_pm_codes_ma
ster 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_n
o),1,2) || cast(substr(max(vr_n
o),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_am
t,class_of_vr,mon_ab
s,proj_code,pt_code,
debit_tot_am
t)values

(l_vr_no,credit_amou
nt,1,$3,$1,$2,debit_
amount);

insert into irla_punchingmedium_
amt
(amount,codehead,typ
e_of_charge,vr_no) select
amount,irla_code,typ
e_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.

Information transmitted by this e-mail is proprietary to Infinite ComputerSolutions and / or its Customers and is intended for use only by theindividual or the entity to which it is addressed, and may containinformation that is privileged, confidential or exempt from disclosureunder applicable law. If you are not the intended recipient or it appearsthat this mail has been forwarded to you without proper authority, you arenotified that any use or dissemination of this information in any manneris strictly prohibited. In such cases, please notify us immediately atinfo.in@infics.com and delete this email from your records.

Richard Huxton

2006-11-17, 5:24 am

Penchalaiah P. wrote:
> 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 table dummy_table without oids as
....
> update dummy_table set irla_code=l_p where irla_code=''P'';
....
> ERROR: relation with OID 75275 does not exist
>
> CONTEXT: SQL statement "update dummy_table set irla_code= $1 where
> irla_code='P'"

The queries in plpgsql have their query-plans compiled the first time
they are executed. This means that the first time you ran the function
you created dummy_table with an OID of 75275. The second time, it was
re-created with a different OID, but the update query doesn't know this.
Its plan tries to access a table with OID=75275 and fails.

Solution: read up on the EXECUTE <string> statement that allows you to
build dynamic queries.

Also, you might want to read up on creating temporary tables.

HTH
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

A. Kretschmer

2006-11-17, 5:24 am

am Fri, dem 17.11.2006, um 15:15:25 +0530 mailte Penchalaiah P. folgendes:[color=dar
kred]
> 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_cod
e
> numeric,in_pt_code varchar,in_mon_abs date) returns varchar as'
> ...
>
> create table dummy_table without oids as
>
> ...
>
> drop table dummy_table;
> end;
>
> 'language'plpgsql';[
/color]

PG caches the plan for this function and runs in an error, because it
believes the second time, that this table has the oid from the first
run.

You can rewrite your function and use EXECUTE for such statements
(create and drop table). This might be helpful, but i'm not sure.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47215, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links