Re: [SQL] Sum() rows
Yes, I tried it. In this table the query works fine, but in a big table (with aprox. 200.000 records) the query performace is very bad. I tried it (in the example table): SELECT *,(select sum(value) from tb1 as tb1_2 where tb1_2.id<=tb1_1.id) as subtot from tb1 as tb1_1 order by id; In a small table it works fine, but in a bigger table it works very slow. I was thinking to create a temporary table and a function to update the value for each row of the query... something like: CREATE table temporary (id serial primary key,value numeric default 0); INSERT into temporary values (1,0); CREATE or replace function temporary_sum(numeric) returns numeric as $$ BEGIN update temporary set value = value+$1 where id=1; return value from temporary where id=1; END; $$ language 'plpgsql'; Then before execute the query I need to update the table's value to 0. UPDATE temporary set value=0; SELECT *,temporary_sum(value) from tb1; It works better than the "sum() subquery", but it not seems correct. What is the better way??? Is there a sum() function that works how I want??? Thanks. Quoting Bruno Wolff III <[EMAIL PROTECTED]>: Since in your example the id field gives the ordering, you can use a subselect to add up the subtotal for rows with and id less than or equal to the value of id for the current row. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20); insert into tb1 values (2,2); insert into tb1 values (3,3); insert into tb1 values (4,17); insert into tb1 values (5,-0.5); insert into tb1 values (6,3); I want a query that returns: -id- | --- value --- | --- subtot --- 1 |20.00 | 20.00 2 | 2.00 | 22.00 3 | 3.00 | 25.00 4 |17.00 | 42.00 5 |-0.50 | 41.50 6 | 3.00 | 44.50 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Sum() rows
Hi. The function works well... I will use your function and rewrite it to accept more than one select, becouse in this case you selected all records from tb1 table. In real case the table is bigger with many fields and I will work with some filters and some ordering (dynamically)... Thank you. [EMAIL PROTECTED] wrote: CREATE TABLE tb1 (id integer primary key, value numeric); CREATE TYPE subtotal_type AS (id integer, value numeric, subtotal numeric); CREATE OR REPLACE FUNCTION subtotal () RETURNS SETOF subtotal_type AS $$ DECLARE tbrowRECORD; sbrowsubtotal_type; BEGIN sbrow.subtotal := 0; FOR tbrow IN SELECT id, value FROM tb1 ORDER BY id LOOP sbrow.id := tbrow.id; sbrow.value := tbrow.value; sbrow.subtotal := sbrow.subtotal + tbrow.value; RETURN NEXT sbrow; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; insert into tb1 (id, value) values (1, 20.0); insert into tb1 (id, value) values (2, 2.0); insert into tb1 (id, value) values (3, 3.0); select * from subtotal(); [EMAIL PROTECTED] wrote: Hi. How can I sum a row and show the sum for each row??? For example, in a finances table that have the total movimentation(debit/credit) in the bank. i.e: CREATE TABLE TB1 (id integer primary key, value numeric); insert into tb1 values (1,20); insert into tb1 values (2,2); insert into tb1 values (3,3); insert into tb1 values (4,17); insert into tb1 values (5,-0.5); insert into tb1 values (6,3); I want a query that returns: -id- | --- value --- | --- subtot --- 1 |20.00 | 20.00 2 | 2.00 | 22.00 3 | 3.00 | 25.00 4 |17.00 | 42.00 5 |-0.50 | 41.50 6 | 3.00 | 44.50 Any idea??? Thanks. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] View unique rowid
Hi Is it possible to refer to a unique row identifier on a view ? I have the following view but in a subsequent select I need to refer to each row's unique identifier and I know oid's are not valid for a view. create view persontransit as select personid, planet, name as aspectname, position as planetposition, position+angle as transitposition from personplanet, aspect union select personid, planet, name as aspectname, position as planetposition, position-angle as transitposition from personplanet, aspect where name != 'OPPOSITION'; Many thanks David ---(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] View unique rowid
> Is it possible to refer to a unique row identifier on a view ? > > I have the following view but in a subsequent select I need to refer to > each row's unique identifier and I know oid's are not valid for a view. You can get an oid from some table in view definition. May be it results to resolve your problem... You can also try "create temp sequence" (review http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php) select 'saludos'; -- Rafa Couto (caligari) mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] View unique rowid
Many thanks I think the problem with using the oid from the primary table is that may rows in the view definition will get the same oid because of the union. Thanks very much anyway. I will look at the temporary sequence although I am not sure if sequences work with views. Regards David From: Rafa Couto <[EMAIL PROTECTED]> Reply-To: Rafa Couto <[EMAIL PROTECTED]> To: David Klugmann <[EMAIL PROTECTED]> CC: pgsql-sql@postgresql.org Subject: Re: [SQL] View unique rowid Date: Wed, 1 Jun 2005 16:53:19 +0200 > Is it possible to refer to a unique row identifier on a view ? > > I have the following view but in a subsequent select I need to refer to > each row's unique identifier and I know oid's are not valid for a view. You can get an oid from some table in view definition. May be it results to resolve your problem... You can also try "create temp sequence" (review http://archives.postgresql.org/pgsql-sql/2005-05/msg00126.php) select 'saludos'; -- Rafa Couto (caligari) mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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] View unique rowid
O David Klugmann έγραψε στις Jun 1, 2005 : > > Hi > Is it possible to refer to a unique row identifier on a view ? > > I have the following view but in a subsequent select I need to refer to > each row's unique identifier and I know oid's are not valid for a view. Provided your ids are 4 bytes long, you could try to build an artificial id as > > create view persontransit > as > select personid, planet, name as aspectname, position as planetposition, SELECT personid::int8 as viewid, > position+angle as transitposition > from personplanet, aspect > union > select personid, planet, name as aspectname, position as planetposition, SELECT X'1'::int8<<32 | personid::int8 as viewid... > position-angle as transitposition > from personplanet, aspect > where name != 'OPPOSITION'; > > Many thanks > > David > that way the viewid is unique, + you know which part of the view it represents by masking on the 33th bit (4294967296), while you can get the actual personid by masking with X'' (4294967295) > > > ---(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 > -- -Achilleus ---(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] Sum() rows
On Wed, Jun 01, 2005 at 08:49:00 -0300, [EMAIL PROTECTED] wrote: > Yes, > I tried it. In this table the query works fine, but in a big table > (with aprox. > 200.000 records) the query performace is very bad. > I tried it (in the example table): > SELECT *,(select sum(value) from tb1 as tb1_2 where tb1_2.id<=tb1_1.id) as > subtot from tb1 as tb1_1 order by id; > > In a small table it works fine, but in a bigger table it works very slow. Not surprising, since this is probably O(n^2). > What is the better way??? Is there a sum() function that works how I want??? Having the application do the running sum is probably the best way to do it. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] unsubscribe
unsubscribe ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Release feature
Hi Everybody I'd like to know which release of postgresql included the trapping errors sintaxe below BEGIN ... EXCEPTION WHEN OTHERS THEN .. END; I tried to use this sintaxe with 7.4.7 release and did not works! Thanks in advance! Marx Haron ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Release feature
On 6/1/05, Marx Barbosa <[EMAIL PROTECTED]> wrote: > Hi Everybody > > I'd like to know which release of postgresql included the trapping errors > sintaxe below > > BEGIN > ... > EXCEPTION > WHEN OTHERS THEN > .. > END; > > I tried to use this sintaxe with 7.4.7 release and did not works! > > Thanks in advance! > 8.0.x and later -- Atentamente, Jaime Casanova (DBA: DataBase Aniquilator ;) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Convert int to hex
I want to be able to change an int4 from a sequence and store it as varchar in the database as a hex number. Is this possible? Thank you, Fernando ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
