[SQL] Need Postgresql Help
Respected sir, Atul Pedgaonkar here from India. I am using postgresql7.2 as backend and for front-end, perl5.6.1. I need some help regarding postgresql-stored procedure. The problem is like this 1)How to create stored procedure in postgresql? 2)How can i use it from perl script with parameters. ? 3)How to return resultset from that Stored Procedure ? I hope your kind co-operation. Thank You, Regards, Atul…
Re: [SQL] Need Postgresql Help
On Monday 02 December 2002 09:44, Atul wrote: > Respected sir, > Atul Pedgaonkar here from India. I am using postgresql7.2 as > backend and for front-end, perl5.6.1. I need some help regarding > postgresql-stored procedure. The problem is like this > > 1)How to create stored procedure in postgresql? > 2)How can i use it from perl script with parameters. ? > 3)How to return resultset from that Stored Procedure ? You need 7.3 to do 3). Infor on stored procedures: $INSTALLDIR/doc/html/plpgsql.html -- Andreas Joseph Krogh <[EMAIL PROTECTED]> : What does this "kernel" program do? - It does automated remote popcorn popping using XML-RPC to communicate bidirectionally with TCP/IP enabled microwave ovens. (Slashdot reply) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Need Postgresql Help
On Monday 02 Dec 2002 8:42 am, Andreas Joseph Krogh wrote: > On Monday 02 December 2002 09:44, Atul wrote: > > Respected sir, > > Atul Pedgaonkar here from India. I am using postgresql7.2 as > > backend and for front-end, perl5.6.1. I need some help regarding > > postgresql-stored procedure. The problem is like this > > > > 1)How to create stored procedure in postgresql? > > 2)How can i use it from perl script with parameters. ? > > 3)How to return resultset from that Stored Procedure ? > > You need 7.3 to do 3). > Infor on stored procedures: > $INSTALLDIR/doc/html/plpgsql.html 1. See the manual chapter on "procedural languages" - plpgsql, pltcl, plperl Also see http://techdocs.postgresql.org 2. Exactly as with any other query 3. Either upgrade to 7.3 (as above) or return a cursor from the function. -- Richard Huxton ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Question about slow Select when using 'IN'.
Hi all, I hope someone can help me out. I'm doing single-table select statements on a large table and I could use some help in speeding it up. My query is of the form: SELECT col, count(col) FROM tab WHERE id IN (3, 4,7,2, ...) GROUP BY COL ORDER BY count for a very large number of rows. I have an index on id, so the explain looks like: Aggregate (cost=12.12..12.14 rows=1 width=5) -> Group (cost=12.12..12.13 rows=4 width=5) -> Sort (cost=12.12..12.12 rows=4 width=5) -> Index Scan using col_id_idx2, col_id_idx2, col_id_idx2, col_id_idx2 on tab (cost=0.00..12.08 rows=4 width=5) So, it does a separate index scan for each row in the IN statement, which takes forever. How do I force the query parser to emulate the behaviour displayed by this query: SELECT col, count(col) FROM tab WHERE (0 = id % 5) GROUP BY COL ORDER BY count Aggregate (cost=3.75..3.86 rows=2 width=5) -> Group (cost=3.75..3.81 rows=21 width=5) -> Sort (cost=3.75..3.75 rows=21 width=5) -> Index Scan using col_id_idx2 on tab (cost=0.00..3.29 rows=21 width=5) Which only does one index scan for an equivelant number of records. Thanks for any help. Please cc to my e-mail. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Need Postgresql Help
Respected sir, Atul Pedgaonkar here from india. I am using postgresql7.2 as backend and for front-end ,perl5.6.1. I need some help regarding postgresql stored procedure. The problem is like this 1)How to create stored procedure in postgresql ? 2)How can i use it from perl script with paratmeters. ? 3)How to return resultset from that Stored Procedure ? I hope your kind co-operation. Thank You, Regards, Atul _ Add photos to your e-mail with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Accumulative Queries?
Let's say you have a table of Financial transactions: Create table checks ( id serial, number varchar, to varchar, amount real, date integer ); (date is an epoch timestamp) And you want to get a listing of checks "SELECT * FROM checks ORDER BY date ASC"; but you also want to have an accumulative field that adds up the amount field as the results are returned, so you might see results like: id number to amount date balance 1 0 Deposit -100 12344 100 2 100 Jack 40 123455 60 3 101 Bob 20 123345 40 4 102 VOID 0 0 40 5 103 Harold 11 123488 29 Is this possible using only SQL? Also, assuming you have checks year round, how might you get results only in March that have totals consistent for the time frame while taking into account all the other checks in Jan and Feb? - The best way to predict the future is to invent it. - ---(end of broadcast)--- TIP 3: 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] SQL99
Hi there! Can anybody explain behavior of ? Or give a link where explanation can be found? Or something like that ?:) To my regret, i can't figure it out right from SQL standard description with my "brute force". regards, --- .evgen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] Min and Max
Hello, everybody! I've trouble to make a "simple"(?) query... The following table is an example: table: children id_father | child_name | child_age --++ 1 | John | 2 1 | Joe| 3 1 | Mary | 4 1 | Cristine | 4 2 | Paul | 1 2 | Stephany | 2 2 | Raul | 5 How can I get the rows of the children name and its "father" such that they have the min child_ages? I expect the following rows as result: id_father | child_name | child_age --++ 1 | John | 2 2 | Paul | 1 The same for the max child_ages... id_father | child_name | child_age --++ 1 | Mary | 4 1 | Cristine | 4 2 | Raul | 5 I tried to use min() and max() with group by but I could not get the expected results: -- the following does not return the child_name... select id_father, min(child_age) from children group by id_father; select id_father, max(child_age) from children group by id_father; Any suggestions? Thanks In Advance, Sergio Oshiro ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Min and Max
On 29 Nov 2002, Sergio Oshiro wrote: > How can I get the rows of the children name and its "father" such that > they have the min child_ages? > > -- the following does not return the child_name... > select id_father, min(child_age) from children group by id_father; > select id_father, max(child_age) from children group by id_father; You could join one of the above with the table itself and get the result. Something like select * from ( select id_father, min(child_age) from children group by id_father) as r, children where children.id_father = r.id_father and children.min = r.min; -- /Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Min and Max
On Fri, Nov 29, 2002 at 10:55:54AM -0800, Sergio Oshiro wrote: > Hello, everybody! > > I've trouble to make a "simple"(?) query... > > The following table is an example: > > table: children > id_father | child_name | child_age > --++ > 1 | John | 2 > 1 | Joe| 3 > 1 | Mary | 4 > 1 | Cristine | 4 > 2 | Paul | 1 > 2 | Stephany | 2 > 2 | Raul | 5 > > How can I get the rows of the children name and its "father" such that > they have the min child_ages? I expect the following rows as result: > > id_father | child_name | child_age > --++ > 1 | John | 2 > 2 | Paul | 1 > > The same for the max child_ages... > > id_father | child_name | child_age > --++ > 1 | Mary | 4 > 1 | Cristine | 4 > 2 | Raul | 5 select distinct on (id_father) * from children order by id_father, child_age; will give your results select distinct on (id_father) * from children order by id_father, child_age desc; will give the oldest children, but it doesn't list both mary and christine -- it arbitrarily lists mary (you could add child_name to sort order so it wouldn't be abitrary, but it still won't list both). this is a weird use of distinct on, though, and perhaps cheating. a canonical, if slower solution (and one that fixes the tie for oldest child) is: select id_father, child_name, child_age from children c1 where not exists (select * from children c2 where c1.id_father=c2.id_father and c2.child_age > c1.child_age); swap the '>' to '<' for youngest. - J. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 3: 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] Combining queries while preserving order in SQL - Help!
Hi there, I need to do the following in one SQL query: select field1, field2, field3, field4, field5 from table where field6 < 5 order by field1 And a totals line which shows the sum for each column. The important part is that I need to preserve the order by of the first query. Is there any way to do this in one query? Thank you, -- Casey Allen Shobe, Open Source Software Solutions [EMAIL PROTECTED] / http://www.osss.net / 770-653-4526 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Accumulative Queries?
On Sat, Nov 30, 2002 at 02:40:08PM -0800, Benjamin Smith wrote: > Let's say you have a table of Financial transactions: > > Create table checks ( > id serial, > number varchar, > to varchar, > amount real, > date integer > ); > > (date is an epoch timestamp) > > And you want to get a listing of checks > > "SELECT * FROM checks ORDER BY date ASC"; > > but you also want to have an accumulative field that adds up the amount field as the >results are returned, so you might see results like: > > id number to amount date balance > 1 0 Deposit -100 12344 100 > 2 100 Jack 40 123455 60 > 3 101 Bob 20 123345 40 > 4 102 VOID 0 0 40 > 5 103 Harold 11 123488 29 > > Is this possible using only SQL? > > Also, assuming you have checks year round, how might you get results only in March >that have totals consistent for the time frame while taking into account all the >other checks in Jan and Feb? create table checks ( id serial primary key, num varchar unique, "to" varchar, amt real, date date ); insert into checks (num, "to", amt, date) values (0,'deposit',100,'2002-01-01'); insert into checks (num, "to", amt, date) values (0,'jack',40,'2002-02-01'); insert into checks (num, "to", amt, date) values (101,'jack',40,'2002-02-01'); insert into checks (num, "to", amt, date) values (102,'bob',20,'2002-02-01'); insert into checks (num, "to", amt, date) values (103,'VOID',0,'2002-02-01'); insert into checks (num, "to", amt, date) values (104,'jenny',10,'2002-03-01'); insert into checks (num, "to", amt, date) values (104,'raul',10,'2002-03-02'); insert into checks (num, "to", amt, date) values (105,'raul',10,'2002-03-02'); select *, ( select sum(amt) from checks c2 where c2.id<=c1.id as c2) from checks c1; will give you the full accounting. To get just March, put a where-date-between clause in both the outer and inner queries. This will run slowly, though, for many transactions. Either consider: * "closing" an account every month/quarter/year/whenever will the aggregate-so-far, and having your query use that, and do the math from that point onwards * store the running balance in the table, and use triggers to keep it up to date for inserts/updates/deletes -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 3: 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
Re: [SQL] Combining queries while preserving order in SQL - Help!
On Mon, Dec 02, 2002 at 01:46:38PM -0500, Casey Allen Shobe wrote: > Hi there, > > I need to do the following in one SQL query: > > select field1, field2, field3, field4, field5 from table where field6 < 5 > order by field1 > > And a totals line which shows the sum for each column. > > The important part is that I need to preserve the order by of the first query. > > Is there any way to do this in one query? create table foo (a int, b int, c int ); insert into foo values (1,2,3); insert into foo values (4,5,6); select '' as label, * from foo union all select 'TOTAL', sum(a), sum(b), sum(c) from foo order by 1,2; (you wouldn't need the label column to sort by, except that a might contain negative numbers, so the sum might be _less_ than some/all of the numbers. by first sorting on this junk column, we can force the totals at the bottom). p.s. don't forget the "union __all__", otherwise you'll get rid of duplicate entries in the your table. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Min and Max
If you do not mind non standard, how about: SELECT DISTINCT ON(id_father) * FROM children ORDER BY id_father, child_age; Dennis Björklund wrote: > > On 29 Nov 2002, Sergio Oshiro wrote: > > > How can I get the rows of the children name and its "father" such that > > they have the min child_ages? > > > > -- the following does not return the child_name... > > select id_father, min(child_age) from children group by id_father; > > select id_father, max(child_age) from children group by id_father; > > You could join one of the above with the table itself and get the result. > Something like > > select * > from ( select id_father, min(child_age) > from children > group by id_father) as r, > children > where children.id_father = r.id_father >and children.min = r.min; > > -- > /Dennis > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Question about slow Select when using 'IN'.
Mike Winter writes: > My query is of the form: > SELECT col, count(col) FROM tab WHERE id IN (3, > 4,7,2, ...) GROUP BY COL ORDER BY count > for a very large number of rows. > I have an index on id, so the explain looks like: > Aggregate (cost=12.12..12.14 rows=1 width=5) > -> Group (cost=12.12..12.13 rows=4 width=5) > -> Sort (cost=12.12..12.12 rows=4 width=5) > -> Index Scan using col_id_idx2, col_id_idx2, col_id_idx2, > col_id_idx2 on tab (cost=0.00..12.08 rows=4 width=5) The planner obviously does not think this is a large table (the cost estimates correspond to very small numbers of pages). I wonder whether you have ever VACUUMed or ANALYZEd the table. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] CURRENT_TIMSTAMP
On Mon, Dec 02, 2002 at 11:41:33AM -0500, Raymond Chui wrote:
> I created a column, dada type timstamp with time zone
> and with default CURRENT_TIMSTAMP
> it shows me the default is
>
> default ('now'::text)::timstamp(6) with time zone
>
> Then when I insert a row, the default timestamp value is
>
> -mm-dd HH:MM:ss.m+00
>
> where m is milliseconds.
> How do I make default only -mm-dd HH:MM:ss+00 ?
> Thank Q!
The problem isn't CURRENT_TIMESTAMP, it's your table definition.
If you create the field as timestamp(0) [in 7.3, that's timestamp(0)
with time zone, since the default has swung to no-TZ], it will keep
track of just HMS. Or put in other values for 0 for more granularity on
seconds.
Of course, you can always store the specific time and select it out
w/less resolution (using the time/date functions). In some cases, this
might be a better solution.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CURRENT_TIMSTAMP
On Mon, 2 Dec 2002, Raymond Chui wrote:
> I created a column, dada type timstamp with time zone
> and with default CURRENT_TIMSTAMP
> it shows me the default is
>
> default ('now'::text)::timstamp(6) with time zone
>
> Then when I insert a row, the default timestamp value is
>
> -mm-dd HH:MM:ss.m+00
>
> where m is milliseconds.
> How do I make default only -mm-dd HH:MM:ss+00 ?
As already suggested you probably want a timestamp with time zone(0)
column, but if you really want the ability to insert partial seconds but
the default to not have partial seconds, you can use
CURRENT_TIMESTAMP(0) I believe.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] CURRENT_TIMSTAMP
I created a column, dada type timstamp with time zone
and with default CURRENT_TIMSTAMP
it shows me the default is
default ('now'::text)::timstamp(6) with time zone
Then when I insert a row, the default timestamp value is
-mm-dd HH:MM:ss.m+00
where m is milliseconds.
How do I make default only -mm-dd HH:MM:ss+00 ?
Thank Q!
begin:vcard
n:Chui;Raymond
tel;work:301-713-0640 x168
x-mozilla-html:FALSE
url:http://www.nws.noaa.gov/
org:NWS, NOAA;OHD13
version:2.1
email;internet:[EMAIL PROTECTED]
title:CS
adr;quoted-printable:;;1325 East-West Highway=0D=0ASSMC2, Room 8112;Silver Spring;MD;20910-3282;U.S.A.
fn:Raymond Chui
end:vcard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
