[SQL] hi may i know y am i getting this error
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,''-mm-dd''))||''/''|| extract(year from to_date(npoaa.mon_abs,''-mm-dd'')) =extract(month from to_date($1,''-mm-dd''))||''/''|| extract(year from to_date($1,''-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_am t)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-(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 [EMAIL PROTECTED] and delete this mail from your records. 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 the 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 [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] hi may i know y am i getting this error
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 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
Re: [SQL] hi may i know y am i getting this error
am Fri, dem 17.11.2006, um 15:15:25 +0530 mailte Penchalaiah P. folgendes: > 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' > ... > > create table dummy_table without oids as > > ... > > drop table dummy_table; > end; > > 'language'plpgsql'; 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
[SQL] How convert UNICODE
Hello I have 2 databases with same table. First database is in UNICODE, second in SQL_ASCII. 4 columns from first database I must convert to: (first column)iso8859-1, (second column)iso8859-2, (3-th column)iso8859-1, (4 column)iso8859-5. After it I must save these 4 rows in database in SQL_ASCII. How can I do it? It can be using libpq,libpgeasy, Thx. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] query faster using LEFT OUTER join?
I have a planner question about a multi-join query, where if I
rewrite the query excluding the table that is left joined to the
other tables my query speed increases by 75%.
I've tested this query on both 8.0.9 and 8.2b3, w/ similar results
(except 8.2b3 is 50% faster!)
I'm wondering why the first query is planned a faster way and if
there is anything I could do to my 2nd query faster (which should be
faster because it doesn't have to scan the first table).
Here's my initial query that runs in 22.961ms:
SELECT
train_family_id, object_type, COUNT(*) AS frequency
FROM
translation_pair tp
LEFT OUTER JOIN instance i USING(translation_pair_id)
JOIN context c USING(context_id)
JOIN loc_submission ls USING(loc_submission_id)
JOIN loc_submission_train_info lsti USING(loc_submission_id)
WHERE
translation_pair_id IN (640352, 6144, 1023028, 18155, 240244,
50157)
GROUP BY
train_family_id,
object_type;
If I just remove the translation_pair table and join directly against
the instance table, the query speed drops 50% to 36.6ms
SELECT
train_family_id, object_type, COUNT(*) AS frequency
FROM
instance i
JOIN context c USING(context_id)
JOIN loc_submission ls USING(loc_submission_id)
JOIN loc_submission_train_info lsti USING(loc_submission_id)
WHERE
translation_pair_id IN (640352, 6144, 1023028, 18155, 240244,
50157)
GROUP BY
train_family_id,
object_type;
Looking at the query plans for the 2 of them, it looks like the inner
"Bitmap Index Scan on instance_translation_pair_id" is much slower
when using "IN (x,y,z)" instead of comparing against table
(tp.translation_pair_id = i.translation_pair_id.
Is there anything else I should notice? Can I force the planner to
use the translation_pair table first?
Here's the plan for the first query (using 8.2b3)
---
HashAggregate (cost=3223.17..3223.41 rows=19 width=18) (actual
time=34.913..34.978 rows=122 loops=1)
-> Nested Loop Left Join (cost=29.53..3223.03 rows=19 width=18)
(actual time=0.606..33.994 rows=770 loops=1)
-> Nested Loop Left Join (cost=29.53..3108.80 rows=19
width=22) (actual time=0.552..26.918 rows=770 loops=1)
-> Nested Loop Left Join (cost=29.53..2995.26
rows=19 width=18) (actual time=0.497..19.924 rows=768 loops=1)
-> Nested Loop Left Join (cost=29.53..2879.59
rows=19 width=8) (actual time=0.431..6.395 rows=768 loops=1)
-> Bitmap Heap Scan on translation_pair
tp (cost=24.27..48.10 rows=6 width=4) (actual time=0.304..0.376
rows=6 loops=1)
Recheck Cond: (translation_pair_id
= ANY ('{640352,6144,1023028,18155,240244,50157}'::oid[]))
-> Bitmap Index Scan on
translation_pair_pkey (cost=0.00..24.27 rows=6 width=0) (actual
time=0.254..0.254 rows=6 loops=1)
Index Cond:
(translation_pair_id = ANY
('{640352,6144,1023028,18155,240244,50157}'::oid[]))
-> Bitmap Heap Scan on instance i
(cost=5.26..470.38 rows=123 width=12) (actual time=0.119..0.922
rows=128 loops=6)
Recheck Cond:
(tp.translation_pair_id = i.translation_pair_id)
-> Bitmap Index Scan on
instance_translation_pair_id (cost=0.00..5.26 rows=123 width=0)
(actual time=0.098..0.098 rows=128 loops=6)
Index Cond:
(tp.translation_pair_id = i.translation_pair_id)
-> Index Scan using context_pkey on context c
(cost=0.00..6.07 rows=1 width=18) (actual time=0.015..0.016 rows=1
loops=768)
Index Cond: (i.context_id = c.context_id)
-> Index Scan using loc_submission_train_info_pkey
on loc_submission_train_info lsti (cost=0.00..5.96 rows=1 width=8)
(actual time=0.007..0.008 rows=1 loops=768)
Index Cond: (i.loc_submission_id =
lsti.loc_submission_id)
-> Index Scan using loc_submission_pkey on loc_submission
ls (cost=0.00..6.00 rows=1 width=4) (actual time=0.007..0.008 rows=1
loops=770)
Index Cond: (i.loc_submission_id = ls.loc_submission_id)
And here's the plan for the second query without the first LEFT OUTER
join (using 8.2b3)
HashAggregate (cost=8927.87..8936.89 rows=721 width=18) (actual
time=39.484..39.551 rows=122 loops=1)
-> Nested Loop Left Join (cost=3706.71..8922.47 rows=721
width=18) (actual time=25.246..38.654 rows=770 loops=1)
-> Hash Left Join (cost=3706.
Re: [SQL] How convert UNICODE
How about pg_dump the data from your old database, then load it into your new database which is using UTF8? Drew On Nov 17, 2006, at 9:09 AM, lms wrote: Hello I have 2 databases with same table. First database is in UNICODE, second in SQL_ASCII. 4 columns from first database I must convert to: (first column)iso8859-1, (second column)iso8859-2, (3-th column)iso8859-1, (4 column)iso8859-5. After it I must save these 4 rows in database in SQL_ASCII. How can I do it? It can be using libpq,libpgeasy, Thx. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] How convert UNICODE
Hi New database must be in SQL_ASCII. In old database (UNICODE) are for example 2 columns. Rows from first column I must convert to ISO_8859_2 and insert it to first column in SQL_ASCII database, and rows from second column I must convert to IS0_8859_5 and insert to second column in SQL_ASCII database. On Fri, 17 Nov 2006 13:17:05 -0800, Drew wrote > How about pg_dump the data from your old database, then load it into > your new database which is using UTF8? > > Drew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] sql problem with join
On 11/15/06, Luca Ferrari <[EMAIL PROTECTED]> wrote: On Wednesday 15 November 2006 16:20 your cat, walking on the keyboard, wrote: > May be you could show the results you are getting and then make up some > results that you would really like to get. I am not entirely clear what > you are trying to achieve. Lastly I found the solution, for the moment, that should be the following: select * from peopleRole pr left join roleSkill rs on rs.id_role = pr.id_role left join peopleSkill ps on ps.id_skill = rs.id_skill I think it should go, don't you think? This should list out all roles and their required skills for each person. It will show you the desired level and the evaluated level. select p.*, r.*, rs.desired_level, coalesce(ps.evaluated_level, 0) as evaluated_level from people p inner join peopleRole pr on (p.id_person = pr.id_person) inner join role r on (pr.id_role = r.id_role) inner join roleSkill rs on (r.id_role = rs.id_role) inner join skill s on (rs.id_skill = s.id_skill) left outer join peopleSkill ps on ( p.id_person = ps.id_person and s.id_skill = ps.id_skill ) You will have to check it for syntax problems since I didn't run it. -Aaron -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
Re: [SQL] How convert UNICODE
Hi Yes, but I must convert diffrent columns to diffrent iso-codes. With pg_dump I can only dump all columns from database and these all columns convert only to one iso-code. On Fri, 17 Nov 2006 14:11:25 -0500, Travis Whitton wrote > There might be better ways, but you could dump the data and use the iconv utility. I frequently use it to convert to UTF-8 before loading data. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Random()
On 11/16/06, A. Kretschmer <[EMAIL PROTECTED]> wrote: am Thu, dem 16.11.2006, um 16:31:14 -0200 mailte Ezequias Rodrigues da Rocha folgendes: > Hi list, > > I have a bigint collumn and I would like to generate a random number within the > numbers of my column. select from order by random() limit 1; Is this a PostgreSQL thing or is it SQL standard? -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ==
