Re: [SQL] PostgreSQL and Delphi 6
we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is the server side cursor = doesn't work properly ... so we are using client side for datasets :) On Wed, 15 Jun 2005 23:49:29 -0400, Postgres Admin <[EMAIL PROTECTED]> wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] funny update, say update 1, updated 1 added 2nd.
I have been having some trouble with a particular table view. An UPDATE command is not only changing the applicable record it is also creating a new record as well. wholesale=# select * from accounts_supplier; id | name | contact | addr| addr2 | town | postcode | state | phone | fax | account_type +--+-+---+---++--+---+---+-+-- 1 | ABC construction | TOM | | || | NSW | | | Cash Only 2 | test | | | || | | | | 7 Day 3 | build-4-U| boss| somewhere | | back of beyond | | | | | 7 Day (3 rows) wholesale=# update accounts_supplier set addr='nowhere' where id=3; UPDATE 1 wholesale=# select * from accounts_supplier; id | name | contact | addr | addr2 | town | postcode | state | phone | fax | account_type +--+-+-+---++--+---+---+-+-- 1 | ABC construction | TOM | | || | NSW | | | Cash Only 2 | test | | | || | | | | 7 Day 6 | build-4-U| boss| nowhere | | back of beyond | | | | | 7 Day 3 | build-4-U| boss| nowhere | | back of beyond | | | | | 7 Day (4 rows) Can anyone tell me why this is happening and how to fix it. Here are the table and view definitions. CREATE TABLE account_type ( number smallint, name character varying(20) ); CREATE TABLE address ( addr character varying(40), addr2 character varying(40), town character varying(20), postcode character varying(10), state character(4) ); CREATE TABLE supplier ( id bigserial NOT NULL, name character varying(40), phone character varying(20), fax character varying(20), contact character varying(40), account_type smallint DEFAULT 0 ) INHERITS (address); CREATE VIEW accounts_supplier AS SELECT supplier.id, supplier.name, supplier.contact, supplier.addr, supplier.addr2, supplier.town, supplier.postcode, supplier.state, supplier.phone, supplier.fax, account_type.name AS account_type FROM supplier, account_type WHERE (account_type.number = supplier.account_type); CREATE RULE accounts_supplier_update AS ON UPDATE TO accounts_supplier DO INSTEAD UPDATE supplier SET name = new.name, contact = new.contact, addr = new.addr, addr2 = new.addr2, town = new.town, postcode = new.postcode, state = upper((new.state)::text), phone = new.phone, fax = new.fax, account_type = (SELECT account_type.number FROM account_type WHERE ((account_type.name)::text = (new.account_type)::text)) WHERE (supplier.id = new.id); wholesale=# select version(); version -- PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red Hat 3.4.3-22) (1 row) wholesale=# select * from account_type; number | name +--- 0 | Cash Only 1 | 7 Day 2 | 30 Day 3 | 60 Day 4 | 90 Day (5 rows) Thanks for any help Regards Neil. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL and Delphi 6
So you installed psqlodbc 8 on the client machine with Delphi installed, correct? What problems did you have with cursors? Any other suggestions? Thanks a lot for the help! J Din Adrian wrote: > we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is > the server side cursor = doesn't work properly ... so we are using > client side for datasets :) > > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Function does not return, but gives error..
I have the following function to determine wether or not a user is member of a group, however I have a small problem with it: a group without members results in groupres being NULL (I have checked this), however IF groupres = NULL THEN ... END IF; is not trapped... I have tried to use array_upper(groupres,1) < 1 OR array_upper(groupres,1) = NULL yet, I get no message about it... It is just that I find this strange behaviour, I could find a way to work around this with the if before the loop: Anyone any idea? TIA, Michiel --- function is_in_group(name,name) --- CREATE OR REPLACE FUNCTION "public"."is_in_group" (name, name) RETURNS boolean AS $body$ DECLARE useridINTEGER; groupres INTEGER[]; username ALIAS FOR $1; groupname ALIAS FOR $2; BEGIN SELECT INTO userid usesysid FROM pg_user WHERE usename = $1; IF NOT FOUND THEN RETURN false; -- not a known user, so the user is not a member of the group END IF; SELECT INTO groupres grolist FROM pg_group WHERE groname = $2; IF NOT FOUND THEN RAISE WARNING 'Unknown group ''%''', $2; RETURN false; END IF; IF groupres = NULL THEN -- no members in the group, so this user is not member either RAISE WARNING 'Group ''%'' has no members.', $2; RETURN false; END IF; RAISE WARNING 'Groupres: %',groupres; IF array_lower(groupres,1) >= 1 THEN FOR currentgroup IN array_lower(groupres,1)..array_upper(groupres,1) LOOP IF groupres[currentgroup] = userid THEN RETURN true; END IF; END LOOP; END IF; -- if we can get here, the user was not found in the group -- so we return false RETURN false; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --- end function --- ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] PostgreSQL and Delphi 6
Yes, the client must have the psqlodbc driver and mdac at least 2.6. (Mdac2.5 is verry buggy - attention win2000 and win9x must be upgraded if you need mdac for your app). When using server side cursors for a dataset the update and delete functions act 'strange' as not refreshing corect the affected rows or by showing ony ane record for 20 times instead of 20 different records ... (this test was done with psqlodbc8.0 - postgresql DB 8.0 - I think my colegs didn't test it with 8.1 yet !! ). So we are using client side cursors - slower then server side cursors, but we are satified with the results (we are developing a big ERP app for two years - it also works over internet on 2-3 clients with relative slow net connections : 56-128 Kb/s). Adrian Din, Om Computer & Software, Bucuresti,Romania On Thu, 16 Jun 2005 08:09:56 -0400, Postgres Admin <[EMAIL PROTECTED]> wrote: So you installed psqlodbc 8 on the client machine with Delphi installed, correct? What problems did you have with cursors? Any other suggestions? Thanks a lot for the help! J Din Adrian wrote: we are using postgresql8 +psqlodbc8+ delphi7 ... the only problem is the server side cursor = doesn't work properly ... so we are using client side for datasets :) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Function does not return, but gives error..
> -Original Message- > From: [EMAIL PROTECTED] > Sent: Thu, 16 Jun 2005 14:26:39 +0200 > To: pgsql-sql@postgresql.org > Subject: [SQL] Function does not return, but gives error.. > > I have the following function to determine wether or not a user is > member of a group, however I have a small problem with it: > a group without members results in groupres being NULL (I have checked > this), however > IF groupres = NULL > THEN change it to IF groupres is NULL THEN > > END IF; > is not trapped... I have tried to use array_upper(groupres,1) < 1 OR > array_upper(groupres,1) = NULL > yet, I get no message about it... It is just that I find this strange > behaviour, I could find a way to work around this with the if before the > loop: > > Anyone any idea? > > TIA, > Michiel > --- function is_in_group(name,name) --- > CREATE OR REPLACE FUNCTION "public"."is_in_group" (name, name) RETURNS > boolean AS > $body$ > DECLARE >useridINTEGER; >groupres INTEGER[]; >username ALIAS FOR $1; >groupname ALIAS FOR $2; > BEGIN > SELECT INTO userid usesysid FROM pg_user WHERE usename = $1; > > IF NOT FOUND > THEN > RETURN false; -- not a known user, so the user is not a member > of the group > END IF; > > SELECT INTO groupres grolist FROM pg_group WHERE groname = $2; > > IF NOT FOUND > THEN > RAISE WARNING 'Unknown group ''%''', $2; > RETURN false; > END IF; > > IF groupres = NULL > THEN > -- no members in the group, so this user is not member either > RAISE WARNING 'Group ''%'' has no members.', $2; > RETURN false; > END IF; > RAISE WARNING 'Groupres: %',groupres; > > IF array_lower(groupres,1) >= 1 > THEN > FOR currentgroup IN > array_lower(groupres,1)..array_upper(groupres,1) LOOP >IF groupres[currentgroup] = userid >THEN >RETURN true; >END IF; > END LOOP; > END IF; > > -- if we can get here, the user was not found in the group > -- so we return false > > RETURN false; > END; > $body$ > LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; > --- end function --- > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend with regards, S.Gnanavel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Function does not return, but gives error..
Gnanavel Shanmugam wrote: -Original Message- From: [EMAIL PROTECTED] Sent: Thu, 16 Jun 2005 14:26:39 +0200 To: pgsql-sql@postgresql.org Subject: [SQL] Function does not return, but gives error.. I have the following function to determine wether or not a user is member of a group, however I have a small problem with it: a group without members results in groupres being NULL (I have checked this), however IF groupres = NULL THEN change it to IF groupres is NULL THEN Thanks for the quick response, however that does not help either... END IF; is not trapped... I have tried to use array_upper(groupres,1) < 1 OR array_upper(groupres,1) = NULL yet, I get no message about it... It is just that I find this strange behaviour, I could find a way to work around this with the if before the loop: Anyone any idea? TIA, Michiel --- function is_in_group(name,name) --- CREATE OR REPLACE FUNCTION "public"."is_in_group" (name, name) RETURNS boolean AS $body$ DECLARE useridINTEGER; groupres INTEGER[]; username ALIAS FOR $1; groupname ALIAS FOR $2; BEGIN SELECT INTO userid usesysid FROM pg_user WHERE usename = $1; IF NOT FOUND THEN RETURN false; -- not a known user, so the user is not a member of the group END IF; SELECT INTO groupres grolist FROM pg_group WHERE groname = $2; IF NOT FOUND THEN RAISE WARNING 'Unknown group ''%''', $2; RETURN false; END IF; IF groupres = NULL THEN -- no members in the group, so this user is not member either RAISE WARNING 'Group ''%'' has no members.', $2; RETURN false; END IF; RAISE WARNING 'Groupres: %',groupres; IF array_lower(groupres,1) >= 1 THEN FOR currentgroup IN array_lower(groupres,1)..array_upper(groupres,1) LOOP IF groupres[currentgroup] = userid THEN RETURN true; END IF; END LOOP; END IF; -- if we can get here, the user was not found in the group -- so we return false RETURN false; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --- end function --- ---(end of broadcast)--- TIP 8: explain analyze is your friend with regards, S.Gnanavel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Function does not return, but gives error..
On Thu, Jun 16, 2005 at 02:26:39PM +0200, M.D.G. Lange wrote: > > IF groupres = NULL > THEN > ... > END IF; > is not trapped... Be sure to understand how NULL works in comparisons: http://www.postgresql.org/docs/8.0/static/functions-comparison.html SELECT NULL = NULL; ?column? -- (1 row) SELECT (NULL = NULL) IS TRUE; ?column? -- f (1 row) SELECT (NULL = NULL) IS FALSE; ?column? -- f (1 row) SELECT (NULL = NULL) IS NULL; ?column? -- t (1 row) -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Function does not return, but gives error..
my bad: please forget my previous mail, I tested it with a filled group, which is of course not empty... the solution was correct, thanks! Gnanavel Shanmugam wrote: -Original Message- From: [EMAIL PROTECTED] Sent: Thu, 16 Jun 2005 14:26:39 +0200 To: pgsql-sql@postgresql.org Subject: [SQL] Function does not return, but gives error.. I have the following function to determine wether or not a user is member of a group, however I have a small problem with it: a group without members results in groupres being NULL (I have checked this), however IF groupres = NULL THEN change it to IF groupres is NULL THEN END IF; is not trapped... I have tried to use array_upper(groupres,1) < 1 OR array_upper(groupres,1) = NULL yet, I get no message about it... It is just that I find this strange behaviour, I could find a way to work around this with the if before the loop: Anyone any idea? TIA, Michiel --- function is_in_group(name,name) --- CREATE OR REPLACE FUNCTION "public"."is_in_group" (name, name) RETURNS boolean AS $body$ DECLARE useridINTEGER; groupres INTEGER[]; username ALIAS FOR $1; groupname ALIAS FOR $2; BEGIN SELECT INTO userid usesysid FROM pg_user WHERE usename = $1; IF NOT FOUND THEN RETURN false; -- not a known user, so the user is not a member of the group END IF; SELECT INTO groupres grolist FROM pg_group WHERE groname = $2; IF NOT FOUND THEN RAISE WARNING 'Unknown group ''%''', $2; RETURN false; END IF; IF groupres = NULL THEN -- no members in the group, so this user is not member either RAISE WARNING 'Group ''%'' has no members.', $2; RETURN false; END IF; RAISE WARNING 'Groupres: %',groupres; IF array_lower(groupres,1) >= 1 THEN FOR currentgroup IN array_lower(groupres,1)..array_upper(groupres,1) LOOP IF groupres[currentgroup] = userid THEN RETURN true; END IF; END LOOP; END IF; -- if we can get here, the user was not found in the group -- so we return false RETURN false; END; $body$ LANGUAGE 'plpgsql' STABLE CALLED ON NULL INPUT SECURITY INVOKER; --- end function --- ---(end of broadcast)--- TIP 8: explain analyze is your friend with regards, S.Gnanavel ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] PostgreSQL and Delphi 6
Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi !
This is not possible because the query will have a time interval and the
subtotal will change due the intervals passed to the query...
To get the subtotal I already know how to do it (see below) but the
problem is get the correct output, ordering by the bigger totals
agrouped by product code
CREATE TYPE subtotal_type AS
(code varchar(15),
description varchar(60),
quant float8,
price float8,
total float8,
subtotal float8);
CREATE OR REPLACE FUNCTION product_sales()
RETURNS SETOF subtotal_type AS
$BODY$
DECLARE
tbrow RECORD;
sbrow subtotal_type;
BEGIN
sbrow.subtotal := 0;
FOR tbrow IN
SELECT code, description, quant, price, total FROM test ORDER BY code
LOOP
IF sbrow.code = tbrow.code THEN
sbrow.subtotal := sbrow.subtotal + tbrow.total;
ELSE
sbrow.subtotal := tbrow.total;
END IF;
sbrow.code := tbrow.code;
sbrow.description := tbrow.description;
sbrow.quant := tbrow.quant;
sbrow.price := tbrow.price;
sbrow.total := tbrow.total;
RETURN NEXT sbrow;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
With this function my output is:
dadosadv=# SELECT * FROM product_sales();
code | description | quant | price | total | subtotal
---+-+---+---+---+--
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
(12 rows)
The only problem that I have is that I need to order by the
max(subtotal) aggrouped by code. My desired output is:
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
Any tip?
Regards,
Rodrigo Carvalhaes
Gnanavel Shanmugam wrote:
I think it will be better to add one more column for subtotal and
write an "on before insert" trigger to update the subtotal with sum of
total.
-Original Message-
From: [EMAIL PROTECTED]
Sent: Thu, 16 Jun 2005 00:56:42 -0300
To: pgsql-sql@postgresql.org
Subject: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Guys!
I need to make a complex query. I am thinking to use plpgsql BUT I am
confused how I can solve this.
What I have:
CREATE TABLE test
(
code varchar(15),
description varchar(60),
group varchar(10),
quant float8,
price float8,
total float8
)
WITHOUT OIDS;
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 5, 0.90, 9);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('92110', 'PRODUCT A', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 10, 1.1, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('92190', 'PRODUCT b', 20, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 10, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.8, 80);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 200, 0.8, 160);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.9, 90);
I need an subtotal for all the products with the same group and that the
query be ordered by the bigger subtotal.
For example, I need an output like this:
Ex.
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99120 |
Re: [SQL] PostgreSQL and Delphi 6
We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
This might work,
select * from (SELECT * FROM product_sales()) t order by t.code
desc,t.subtotal;
with regards,
S.Gnanavel
> -Original Message-
> From: [EMAIL PROTECTED]
> Sent: Thu, 16 Jun 2005 10:07:15 -0300
> To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
> Subject: Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
>
> Hi !
>
> This is not possible because the query will have a time interval and the
> subtotal will change due the intervals passed to the query...
> To get the subtotal I already know how to do it (see below) but the
> problem is get the correct output, ordering by the bigger totals
> agrouped by product code
>
> CREATE TYPE subtotal_type AS
>(code varchar(15),
> description varchar(60),
> quant float8,
> price float8,
> total float8,
> subtotal float8);
>
>
> CREATE OR REPLACE FUNCTION product_sales()
> RETURNS SETOF subtotal_type AS
> $BODY$
> DECLARE
> tbrow RECORD;
> sbrow subtotal_type;
>
> BEGIN
> sbrow.subtotal := 0;
> FOR tbrow IN
> SELECT code, description, quant, price, total FROM test ORDER BY code
> LOOP
>
> IF sbrow.code = tbrow.code THEN
> sbrow.subtotal := sbrow.subtotal + tbrow.total;
> ELSE
> sbrow.subtotal := tbrow.total;
> END IF;
> sbrow.code := tbrow.code;
> sbrow.description := tbrow.description;
> sbrow.quant := tbrow.quant;
> sbrow.price := tbrow.price;
> sbrow.total := tbrow.total;
>
> RETURN NEXT sbrow;
> END LOOP;
>
>
> RETURN;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE;
>
> With this function my output is:
>
> dadosadv=# SELECT * FROM product_sales();
> code | description | quant | price | total | subtotal
> ---+-+---+---+---+--
> 92110 | PRODUCT A |10 | 1 |10 | 10
> 92110 | PRODUCT A | 5 | 0.9 | 9 | 19
> 92110 | PRODUCT A | 100 | 0.9 |90 | 109
> 92110 | PRODUCT A |10 | 1.1 |11 | 120
> 92190 | PRODUCT b |10 | 1.1 |11 | 11
> 92190 | PRODUCT b |10 | 1.1 |11 | 22
> 92190 | PRODUCT b |10 | 1.1 |11 | 33
> 92190 | PRODUCT b |20 | 0.8 | 8 | 41
> 99120 | PRODUCT C |10 | 0.8 | 8 |8
> 99120 | PRODUCT C | 100 | 0.8 |80 | 88
> 99120 | PRODUCT C | 200 | 0.8 | 160 | 248
> 99120 | PRODUCT C | 100 | 0.9 |90 | 338
> (12 rows)
>
> The only problem that I have is that I need to order by the
> max(subtotal) aggrouped by code. My desired output is:
>
> code | description | quant | price | total | subtotal
> ---+-+---+---+---+--
> 99120 | PRODUCT C |10 | 0.8 | 8 |8
> 99120 | PRODUCT C | 100 | 0.8 |80 | 88
> 99120 | PRODUCT C | 200 | 0.8 | 160 | 248
> 99120 | PRODUCT C | 100 | 0.9 |90 | 338
> 92110 | PRODUCT A |10 | 1 |10 | 10
> 92110 | PRODUCT A | 5 | 0.9 | 9 | 19
> 92110 | PRODUCT A | 100 | 0.9 |90 | 109
> 92110 | PRODUCT A |10 | 1.1 |11 | 120
> 92190 | PRODUCT b |10 | 1.1 |11 | 11
> 92190 | PRODUCT b |10 | 1.1 |11 | 22
> 92190 | PRODUCT b |10 | 1.1 |11 | 33
> 92190 | PRODUCT b |20 | 0.8 | 8 | 41
>
> Any tip?
>
> Regards,
>
> Rodrigo Carvalhaes
>
>
> Gnanavel Shanmugam wrote:
>
> >I think it will be better to add one more column for subtotal and
> >write an "on before insert" trigger to update the subtotal with sum of
> >total.
> >
> >
> >
> >
> >>-Original Message-
> >>From: [EMAIL PROTECTED]
> >>Sent: Thu, 16 Jun 2005 00:56:42 -0300
> >>To: pgsql-sql@postgresql.org
> >>Subject: [SQL] SELECT with sum on groups ORDERING by the subtotals
> >>
> >>Hi Guys!
> >>
> >>I need to make a complex query. I am thinking to use plpgsql BUT I am
> >>confused how I can solve this.
> >>
> >>What I have:
> >>CREATE TABLE test
> >>(
> >> code varchar(15),
> >> description varchar(60),
> >> group varchar(10),
> >> quant float8,
> >> price float8,
> >> total float8
> >>)
> >>WITHOUT OIDS;
> >>
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92110', 'PRODUCT A', 10, 1, 10);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92110', 'PRODUCT A', 5, 0.90, 9);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92110', 'PRODUCT A', 100, 0.9, 90);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92110', 'PRODUCT A', 10, 1.1, 11);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92190', 'PRODUCT b', 10, 1.1, 11);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92190', 'PRODUCT b', 10, 1.1, 11);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92190', 'PRODUCT b', 10, 1.1, 11);
> >>INSERT INTO test (code, description, quant, price, total) VALUES
> >>('92
Re: [SQL] PostgreSQL and Delphi 6
Hi Adrian, You're right. What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). If you find a better solution, please inform me. Regards, Rodrigo Din Adrian wrote: We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] PostgreSQL and Delphi 6
I am sorry - I don't understand (or my english is bad or I don't know what you mean). What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). So: When the user push the 'post' button the driver automatically refresh the current dataset and the user have to wait many seconds (or min?!) until it's finish. ok? How did you say you avoided this ? Adrian Din, Om Computer & SoftWare Bucuresti, Romania On Thu, 16 Jun 2005 10:29:38 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi Adrian, You're right. What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). If you find a better solution, please inform me. Regards, Rodrigo Din Adrian wrote: We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] SELECT very slow
On Wed, 2005-06-15 at 17:08, Thomas Kellerer wrote:
> PFC wrote on 15.06.2005 22:04:
>
> >
> >> It's not the program or Java. The same program takes about 20 seconds
> >> with Firebird and the exactly same data.
> >
> >
> > Hm, that's still very slow (it should do it in a couple seconds like
> > my PC does... maybe the problem is common to postgres and firebird ?)
> >
> > Try eliminating disk IO by writing a set returning function which
> > returns 100 rows, something simple like just a sequence number and
> > a text value... if this is slow too... i don't know...
>
> > do you have an antivirus or zonealarm or something ?
> Wouldn't that affect all DB access not only PG? And as I said, all other
>
> The 20 seconds are ok. This includes processing of the data in the
> application. If I simply loop over the result set and get each column's
> value without further processing it takes 4 seconds with Firebird.
>
> Basically I'm doing the following:
>
> rs = stmt.executeQuery("select * from foo");
> while (rs.next())
> {
>for (int i=0; i < 4; i++)
>{
> Object o = rs.getObject(i+1);
>}
> }
>
> As I said in my other post, the behaviour/performance in PG is dependent on
> the autocommit setting for the connection.
>
> With autocommit set to false the above code takes about 3 seconds in PG
> but wit autocommit set to true, PG takes 3 minutes! It seems that it also
> is very dependent on the fetchsize (apparently the number of rows that are
> cached by the driver). Anything above 100 seems to slow down the overall
> process.
There's got to be more happening than what this is showing us. A
select, and looping through it, should involve no writes, and therefore
no real performance difference from autocommit versus not. Is there
some underlying trigger on the view or something like that? Some kind
of auditing function?
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [SQL] funny update, say update 1, updated 1 added 2nd.
works fine for me. Do you have any triggers on the tables or other rules? Can you provide a complete SQL script that starts from an empty database. Jim -- Original Message --- From: Neil Dugan <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Sent: Thu, 16 Jun 2005 13:38:58 +1000 Subject: [SQL] funny update, say update 1, updated 1 added 2nd. > I have been having some trouble with a particular table view. An UPDATE > command is not only changing the applicable record it is also creating a > new record as well. > > wholesale=# select * from accounts_supplier; > id | name | contact | addr| addr2 | town | > postcode | state | phone | fax | account_type > +--+-+---+---++--+---+---+-+-- > 1 | ABC construction | TOM | | || >| NSW | | | Cash Only > 2 | test | | | || >| | | | 7 Day > 3 | build-4-U| boss| somewhere | | back of beyond | >| | | | 7 Day > (3 rows) > > wholesale=# update accounts_supplier set addr='nowhere' where id=3; > UPDATE 1 > wholesale=# select * from accounts_supplier; > id | name | contact | addr | addr2 | town | > postcode | state | phone | fax | account_type > +--+-+-+---++--+---+---+-+-- > 1 | ABC construction | TOM | | || > | NSW | | | Cash > Only 2 | test | | | || > | | | | 7 > Day 6 | build-4-U| boss| nowhere | | back of beyond | > | | | | 7 > Day 3 | build-4-U| boss| nowhere | | back of beyond | > | | | | 7 Day > (4 rows) > > Can anyone tell me why this is happening and how to fix it. > > Here are the table and view definitions. > > CREATE TABLE account_type ( > number smallint, > name character varying(20) > ); > > CREATE TABLE address ( > addr character varying(40), > addr2 character varying(40), > town character varying(20), > postcode character varying(10), > state character(4) > ); > > CREATE TABLE supplier ( > id bigserial NOT NULL, > name character varying(40), > phone character varying(20), > fax character varying(20), > contact character varying(40), > account_type smallint DEFAULT 0 > ) > INHERITS (address); > > CREATE VIEW accounts_supplier AS > SELECT supplier.id, > supplier.name, > supplier.contact, > supplier.addr, > supplier.addr2, > supplier.town, > supplier.postcode, > supplier.state, > supplier.phone, > supplier.fax, > account_type.name AS account_type > FROM supplier, account_type > WHERE (account_type.number = supplier.account_type); > > CREATE RULE accounts_supplier_update > AS ON UPDATE TO accounts_supplier > DO INSTEAD UPDATE supplier > SET name = new.name, > contact = new.contact, > addr = new.addr, > addr2 = new.addr2, > town = new.town, > postcode = new.postcode, > state = upper((new.state)::text), > phone = new.phone, > fax = new.fax, > account_type = (SELECT account_type.number > FROM account_type > WHERE ((account_type.name)::text = (new.account_type)::text)) > WHERE (supplier.id = new.id); > > wholesale=# select version(); > version > -- > PostgreSQL 7.4.8 on i386-redhat-linux-gnu, compiled by GCC > i386-redhat-linux-gcc (GCC) 3.4.3 20050227 (Red > Hat 3.4.3-22) > (1 row) > > wholesale=# select * from account_type; > number | name > +--- > 0 | Cash Only > 1 | 7 Day > 2 | 30 Day > 3 | 60 Day > 4 | 90 Day > (5 rows) > > Thanks for any help > Regards Neil. > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq --- End of Original Message --- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT very slow
On 16.06.2005 16:00 Scott Marlowe wrote: > There's got to be more happening than what this is showing us. A > select, and looping through it, should involve no writes, and therefore > no real performance difference from autocommit versus not. Is there > some underlying trigger on the view or something like that? Some kind > of auditing function? That's exactly the code that produced the mentioned timings. This is - according to the JDBC driver's documentation - the expected behaviour. The driver can be set to use cursor based fetching but *only* if autocommit is false. If autocommit is on (or fetch size is zero) then the driver will build the whole result set before returning to the caller. http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor Thomas ---(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] cursor "" does not exist
On Wed, Jun 15, 2005 at 06:45:56PM -0400, Vsevolod (Simon) Ilyushchenko wrote: > While those that fail look like this: > > Request select * from material_pkg.ListCautions_fcn($1,$2) as result B > Response result C SELECT > > Note that the successful ones contain strings "S_1" and "BEGIN", and the > failed ones do not. However, there also are successful queries without > these strings, but they are not "select *" queries. Eg, > I have a feeling it's some idiosyncrasy that I'm not familiar with. Does > "BEGIN" refer to the beginning of a trasaction? Yes. A transaction looks like this: BEGIN; SQL1; SQL2; COMMIT; But it also looks like this: SQL3; because in PostgreSQL, everything is always automatically in a transaction, and a bald SQL statement is just a transaction one statement long. With autocommit off, I think what you get is no COMMIT, but you still get the bald transaction. What you really need is to make sure you're starting a multi-statement transaction every time. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] PostgreSQL and Delphi 6
Hi Adrian! Yes, the user have to wait until the refresh finishes... That's a problem but we didn't found any other solution... Regards, Rodrigo Carvalhaes Din Adrian wrote: I am sorry - I don't understand (or my english is bad or I don't know what you mean). What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). So: When the user push the 'post' button the driver automatically refresh the current dataset and the user have to wait many seconds (or min?!) until it's finish. ok? How did you say you avoided this ? Adrian Din, Om Computer & SoftWare Bucuresti, Romania On Thu, 16 Jun 2005 10:29:38 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi Adrian, You're right. What we did was include one "refresh" button and inserted one configuration that after x seconds the component refresh the screen (query). If you find a better solution, please inform me. Regards, Rodrigo Din Adrian wrote: We tested also (pgExpress Driver) - is faster then psqlodbc but we have a problem with it: it does requery (or refresh? - I don't remember exactly) after every post in database.(for us this is a problem - if you have more then 10.000 in current dataset loaded when you add a new record and post-it you stay 1 min !? ) - for curiosity how do you deal with this posible problem ? Adrian Din, Om Computer & Software, Bucuresti, Romania On Thu, 16 Jun 2005 09:54:21 -0300, grupos <[EMAIL PROTECTED]> wrote: Hi J! We use here vitavoom from Steve Howe (www.vitavoom.com). It's a very good and native alternative. It's paid but not expensive and you will have a very good and qualified technical supporte. Regards, Rodrigo Carvalhaes Postgres Admin wrote: I have a client who wants to use Delphi as a front end to a Database, I would like to use PostgreSQL over MSSQL and have been looking at the psqlodbc project. Will psqlodbc connect with Delphi 6? Basically, I'm wondering if anyone has experience with it? Any help will be appreciated. Thanks, J ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Esta mensagem foi verificada pelo sistema de antivírus e acredita-se estar livre de perigo. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi Gnanavel,
Thanks for your promptly answer. Yes, your solution solves this problem
BUT the point is that I don't wanna a solution that works only if the
codes are in desc order.
For example, if the codes are on the order above:
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 10, 0.8, 8);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.4, 80);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 200, 0.80, 160);
INSERT INTO test (code, description, quant, price, total) VALUES
('99120', 'PRODUCT C', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 10, 1, 10);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 5, 0.9, 9);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 100, 0.9, 90);
INSERT INTO test (code, description, quant, price, total) VALUES
('99100', 'PRODUCT A', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 10, 1.10, 11);
INSERT INTO test (code, description, quant, price, total) VALUES
('99130', 'PRODUCT b', 20, 0.80, 8);
With this data your query result is wrong:
dadosadv=# SELECT * FROM (SELECT * FROM product_sales() ) t order by
t.code desc, t.subtotal;
code | description | quant | price | total | subtotal
---+-+---+---+---+--
99130 | PRODUCT b |10 | 1.1 |11 | 11
99130 | PRODUCT b |10 | 1.1 |11 | 22
99130 | PRODUCT b |10 | 1.1 |11 | 33
99130 | PRODUCT b |20 | 0.8 | 8 | 41
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
99100 | PRODUCT A |10 | 1 |10 | 10
99100 | PRODUCT A | 5 | 0.9 | 9 | 19
99100 | PRODUCT A | 100 | 0.9 |90 | 109
99100 | PRODUCT A |10 | 1.1 |11 | 120
The point is that I wanna that the output always be ordered by the
bigger subtotal groups, indepent of the order of the codes...
Do you have any idea how I can do it?
Thanks,
Rodrigo Carvalhaes
Gnanavel Shanmugam wrote:
This might work,
select * from (SELECT * FROM product_sales()) t order by t.code
desc,t.subtotal;
with regards,
S.Gnanavel
-Original Message-
From: [EMAIL PROTECTED]
Sent: Thu, 16 Jun 2005 10:07:15 -0300
To: [EMAIL PROTECTED], pgsql-sql@postgresql.org
Subject: Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
Hi !
This is not possible because the query will have a time interval and the
subtotal will change due the intervals passed to the query...
To get the subtotal I already know how to do it (see below) but the
problem is get the correct output, ordering by the bigger totals
agrouped by product code
CREATE TYPE subtotal_type AS
(code varchar(15),
description varchar(60),
quant float8,
price float8,
total float8,
subtotal float8);
CREATE OR REPLACE FUNCTION product_sales()
RETURNS SETOF subtotal_type AS
$BODY$
DECLARE
tbrow RECORD;
sbrow subtotal_type;
BEGIN
sbrow.subtotal := 0;
FOR tbrow IN
SELECT code, description, quant, price, total FROM test ORDER BY code
LOOP
IF sbrow.code = tbrow.code THEN
sbrow.subtotal := sbrow.subtotal + tbrow.total;
ELSE
sbrow.subtotal := tbrow.total;
END IF;
sbrow.code := tbrow.code;
sbrow.description := tbrow.description;
sbrow.quant := tbrow.quant;
sbrow.price := tbrow.price;
sbrow.total := tbrow.total;
RETURN NEXT sbrow;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
With this function my output is:
dadosadv=# SELECT * FROM product_sales();
code | description | quant | price | total | subtotal
---+-+---+---+---+--
92110 | PRODUCT A |10 | 1 |10 | 10
92110 | PRODUCT A | 5 | 0.9 | 9 | 19
92110 | PRODUCT A | 100 | 0.9 |90 | 109
92110 | PRODUCT A |10 | 1.1 |11 | 120
92190 | PRODUCT b |10 | 1.1 |11 | 11
92190 | PRODUCT b |10 | 1.1 |11 | 22
92190 | PRODUCT b |10 | 1.1 |11 | 33
92190 | PRODUCT b |20 | 0.8 | 8 | 41
99120 | PRODUCT C |10 | 0.8 | 8 |8
99120 | PRODUCT C | 100 | 0.8 |80 | 88
99120 | PRODUCT C | 200 | 0.8 | 160 | 248
99120 | PRODUCT C | 100 | 0.9 |90 | 338
(12 rows)
The only problem that I have is that I need to order by the
max(subtotal) aggrouped by code. My desired output is:
code
Re: [SQL] SELECT very slow
If autocommit is on (or fetch size is zero) then the driver will build the whole result set before returning to the caller. Sure, but that is not your problem : even building the whole result set should not take longer than a few seconds (I gave you test timings in a previous message). So... what ? What does the taskman say ? CPU at 100% ? how much kernel time ? http://jdbc.postgresql.org/documentation/80/query.html#query-with-cursor Thomas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(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] SELECT very slow
On 16.06.2005 17:29 PFC wrote: >> If autocommit is on (or fetch size is zero) then the driver will >> build the whole >> result set before returning to the caller. > > > Sure, but that is not your problem : even building the whole result > set should not take longer than a few seconds (I gave you test timings > in a previous message). > So... what ? > What does the taskman say ? CPU at 100% ? how much kernel time ? > Taskmanager (I'm on windows) does not show any high CPU usage. And (as I wrote in the initial post) I cannot see any memory increase in the PG process as well (which I would expect with a result set of that size built up in memory). Thomas ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] SELECT with sum on groups ORDERING by the subtotals
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > I need an subtotal for all the products with the same group and that the > query be ordered by the bigger subtotal. (please proofread: the subtotals in your example output did not add up) By "same group" I presume you mean the same code, as you don't actually use the "group varchar(10)" column you created in your example. A major problem you have is that you have no other way of ordering the rows except by the code. So having a running subtotal is fairly pointless, as the items within each code will appear randomly. Since only the grand total for each code is significant, you could write something like this: SELECT t.*, s.subtotal FROM (SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1) s, test t WHERE s.code = t.code ORDER BY subtotal desc; code | description | quant | price | total | subtotal - ---+-+---+---+---+-- 99120 | PRODUCT C |10 | 0.8 | 8 | 338 99120 | PRODUCT C | 100 | 0.8 |80 | 338 99120 | PRODUCT C | 200 | 0.8 | 160 | 338 99120 | PRODUCT C | 100 | 0.9 |90 | 338 92110 | PRODUCT A |10 | 1 |10 | 120 92110 | PRODUCT A | 5 | 0.9 | 9 | 120 92110 | PRODUCT A | 100 | 0.9 |90 | 120 92110 | PRODUCT A |10 | 1.1 |11 | 120 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |10 | 1.1 |11 | 41 92190 | PRODUCT b |20 | 0.8 | 8 | 41 If you don't need all that intermediate stuff: SELECT code, SUM(total) AS subtotal FROM test GROUP BY 1 ORDER BY 2 DESC; code | subtotal - ---+-- 99120 | 338 92110 | 120 92190 | 41 If you do need the other rows, you will have to specify a way of ordering the rows within a code group. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200506161458 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFCscxavJuQZxSWSsgRAubaAKDXtwvbX4FyvSMeOYqCWAYfStv3xgCfX+XM 79gJZ2hUgDk1jL3LDQv3le0= =mpnW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] UPDATEABLE VIEWS ... Examples?
Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't seem to find any examples of this ... Does anyone know of an online example of doing this that I can read through? Thanks ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] UPDATEABLE VIEWS ... Examples?
There is a write up on these at: http://www.varlena.com/GeneralBits/82.php --elein [EMAIL PROTECTED]Varlena, LLCwww.varlena.com PostgreSQL Consulting, Support & Training PostgreSQL General Bits http://www.varlena.com/GeneralBits/ = I have always depended on the [QA] of strangers. On Thu, Jun 16, 2005 at 06:05:03PM -0300, Marc G. Fournier wrote: > > Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer > to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't > seem to find any examples of this ... > > Does anyone know of an online example of doing this that I can read > through? > > Thanks ... > > > Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) > Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(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] UPDATEABLE VIEWS ... Examples?
Here's one I did a while ago; the tables are trivial in this case (and
the whole thing is definitely overkill) so it should make it easier to
digest.
This becomes useful if you use some sort of ORM layer (Class::DBI in my
case) that can be made to recognize the 'type' column and behave
polymorphically.
The nice part is that I can use these classes in my CRUD framework
without any special treatment, the downside is that the whole thing is
just more trouble than it's worth.
At the end I've included a script that generates the rules for you,
given the tables and the view.
CREATE TABLE "abbase"."reagents" (
"reagent_id" serial NOT NULL,
"type"varchar(15) DEFAULT 'base' NOT NULL,
"created" timestamp DEFAULT now() NOT NULL,
"modified"timestamp DEFAULT now() NOT NULL,
"version" smallint DEFAULT 0 NOT NULL,
"batch_id"integer NOT NULL,
"barcode" char(6) NOT NULL
) WITH OIDS;
ALTER TABLE "abbase"."reagents" ADD PRIMARY KEY ("reagent_id");
ALTER TABLE "abbase"."reagents" ADD CONSTRAINT "batch"
FOREIGN KEY ("batch_id")
REFERENCES "abbase"."batches" ("batch_id")
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
ALTER TABLE "abbase"."reagents" ADD CONSTRAINT types CHECK (type IN
('base', 'supernatant'));
CREATE INDEX "idx_reagents_barcode" ON "abbase"."reagents" ("barcode");
CREATE TABLE "abbase"."r_supernatants" (
"supernatant_id" integer NOT NULL,
"vendor_id" varchar(25) NOT NULL
) WITH OIDS;
ALTER TABLE "abbase"."r_supernatants" ADD PRIMARY KEY
("supernatant_id");
ALTER TABLE "abbase"."r_supernatants" ADD CONSTRAINT "reagent"
FOREIGN KEY ("supernatant_id")
REFERENCES "abbase"."reagents" ("reagent_id")
ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE;
CREATE VIEW "abbase"."supernatants" AS
SELECT r.reagent_id AS supernatant_id, r.created, r.modified,
r.version, r.batch_id, r.barcode, s.vendor_id
FROM abbase.reagents r
JOIN abbase.r_supernatants s ON(r.reagent_id = s.supernatant_id)
;
CREATE RULE "supernatants_insert" AS ON INSERT TO
"abbase"."supernatants"
DO INSTEAD (
INSERT INTO "abbase"."reagents" (reagent_id, type,
batch_id, barcode)
VALUES (
COALESCE(NEW.supernatant_id,
nextval('reagents_reagent_id_seq')),
'supernatant',
NEW.batch_id,
NEW.barcode
);
INSERT INTO "abbase"."r_supernatants" (supernatant_id,
vendor_id)
VALUES (
COALESCE(NEW.supernatant_id,
currval('reagents_reagent_id_seq')),
NEW.vendor_id
);
);
CREATE RULE "supernatants_update" AS ON UPDATE TO
"abbase"."supernatants"
DO INSTEAD (
UPDATE "abbase"."reagents" SET
type= 'supernatant',
batch_id= NEW.batch_id,
barcode = NEW.barcode
WHERE reagent_id = OLD.supernatant_id;
UPDATE "abbase"."r_supernatants" SET
vendor_id = NEW.vendor_id
WHERE supernatant_id = OLD.supernatant_id;
);
CREATE RULE "supernatants_delete" AS ON DELETE TO
"abbase"."supernatants"
DO INSTEAD
DELETE FROM "abbase"."reagents" WHERE reagent_id =
OLD.supernatant_id;
CREATE RULE "r_supernatants_delete" AS ON DELETE TO
"abbase"."r_supernatants"
DO
DELETE FROM "abbase"."reagents" WHERE reagent_id =
OLD.supernatant_id;
Here's a script that generated the rules, it's not pretty but seems to
work:
#!/usr/bin/perl -w
use strict;
# autocreate rules for updating multi-table views
use Data::Dumper;
use Getopt::Long;
use IO::All;
use Template;
use POSIX qw(ceil);
my $d_exclude = {
created => 1,
modified=> 1,
version => 1,
};
my $template = Template->new({
INTERPOLATE => 1,
}) || die "$Template::ERROR\n";
my $opts = {
dmitri => 0,
autotype=> 1,
};
GetOptions($opts, 'base=s', 'join=s', 'type=s', 'view=s', 'dmitri',
'primary', 'autotype!');
warn "WARNING: dmitrisms are on, some assumptions may not make sense"
if($opts->{dmitri});
die "need the base class file (--base)" unless($opts->{base});
die "need the join class file (--join)" unless($opts->{join});
unless($opts->{type}){
if($opts->{join} =~ /^\w_(\w+)s\.sql$/){
warn "WARNING: no 'type' specified for class, guessing:
$1";
$opts->{type} = $1;
}
else {
die "need the join class type (--type)";
}
}
unless($opts->{view}){
$opts->{view} = $opts->{type}
Re: [SQL] UPDATEABLE VIEWS ... Examples?
On Thu, Jun 16, 2005 at 06:05:03PM -0300, Marc G. Fournier wrote: > > Reading through the docs, both the CREATE VIEW and CREATE RULE pages refer > to how you can use a RULE to 'simulate' an updateable VIEW ... but I can't > seem to find any examples of this ... Are you looking for "Cooperation with Views" in the "Rules on INSERT, UPDATE, and DELETE" section of the "The Rule System" chapter? http://www.postgresql.org/docs/8.0/static/rules-update.html#RULES-UPDATE-VIEWS -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Still getting autoreplies from list member
The autoreplies mentioned in these threads are still happening: http://archives.postgresql.org/pgsql-sql/2005-06/msg00102.php http://archives.postgresql.org/pgsql-sql/2005-06/msg00097.php I can tweak my filters to drop the autoreplies, but shouldn't this person either fix their mail configuration or be unsubscribed for not playing nicely? Tom Lane mentioned that Marc could look into this -- did anything come of that? http://archives.postgresql.org/pgsql-sql/2005-06/msg00105.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Still getting autoreplies from list member
I'll just second that this is, in fact, extremely annoying. Dmitri -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Thursday, June 16, 2005 11:16 PM To: pgsql-sql@postgresql.org Subject: [SQL] Still getting autoreplies from list member The autoreplies mentioned in these threads are still happening: http://archives.postgresql.org/pgsql-sql/2005-06/msg00102.php http://archives.postgresql.org/pgsql-sql/2005-06/msg00097.php I can tweak my filters to drop the autoreplies, but shouldn't this person either fix their mail configuration or be unsubscribed for not playing nicely? Tom Lane mentioned that Marc could look into this -- did anything come of that? http://archives.postgresql.org/pgsql-sql/2005-06/msg00105.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(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] Still getting autoreplies from list member
On Friday 17 Jun 2005 8:46 am, Michael Fuhr wrote: > The autoreplies mentioned in these threads are still happening: > > http://archives.postgresql.org/pgsql-sql/2005-06/msg00102.php > http://archives.postgresql.org/pgsql-sql/2005-06/msg00097.php > > I can tweak my filters to drop the autoreplies, but shouldn't this > person either fix their mail configuration or be unsubscribed for > not playing nicely? Tom Lane mentioned that Marc could look into > this -- did anything come of that? me too - unless the guy is prime minister of brazil or some other big pot that we cant antagonise -:) -- regards kg http://www.livejournal.com/users/lawgon tally ho! http://avsap.org.in ಇಂಡ್ಲಿನಕ್ಸ வாழ்க! ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] Putting an INDEX on a boolean field?
Does that make sense? Would it ever get used? I can't see it, but figured I'd ask ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Putting an INDEX on a boolean field?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > Does that make sense? Would it ever get used? It could get used if one of the two values is far less frequent than the other. Personally I'd think about a partial index instead ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Putting an INDEX on a boolean field?
how about an very large table with a "processed" type flag? uru -Dave Marc G. Fournier wrote: Does that make sense? Would it ever get used? I can't see it, but figured I'd ask ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Putting an INDEX on a boolean field?
On Fri, 17 Jun 2005, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: Does that make sense? Would it ever get used? It could get used if one of the two values is far less frequent than the other. Personally I'd think about a partial index instead ... Hr, hadn't thought of that ... wouldn't you have to build two indexes (one for true, one for false) for this to be completely effective? unless you know all your queries are going to search for one, but not the other? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Putting an INDEX on a boolean field?
On 6/17/05, Marc G. Fournier <[EMAIL PROTECTED]> wrote: > On Fri, 17 Jun 2005, Tom Lane wrote: > > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > >> Does that make sense? Would it ever get used? > > > > It could get used if one of the two values is far less frequent than the > > other. Personally I'd think about a partial index instead ... > > Hr, hadn't thought of that ... wouldn't you have to build two indexes > (one for true, one for false) for this to be completely effective? unless > you know all your queries are going to search for one, but not the other? > I guess it will be effective only if you know wich value will be less frequent... on the other value a sequential scan will be a win, isn't it? -- regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
