[SQL] Cascading sum in tree with CTE?
Hi . My problem resembles this: I have the following (simplified) tables 1) create table account ( id serial, name varchar, parent_id int4 references account, primary key (id)) 2) create table transaction (id serial, account_id int4 references account, memo varchar, debet, credit, primary key(id)) So a basic ledger system, with a tree of accounts and transactions on them. Some accounts have transactions others doesn't. I have written a CTE which generates the tree, but I would like to append to each line the sum of all debits and credits of this and all sub accounts. I think it sould be doable, but cannot bend my brain around it and my google-fu has come out short. Say the data was: Account: 1, 'Expenses', null 2, 'IRS', 1 3, '7-Eleven' Transaction: 1, 2, 'Tax Jan 10', null, 100 2, 2, 'Tax Feb 10', null, 120 3, 2, 'Tax Feb 10 correction', 10,null 4, 3, 'Sodas', 10, null 5, 3, 'Beer', 5, null I would expect a tree like: 1, Debt, 25, 220 2, IRS, 10, 220 3, 7-eleven, 15, 0 Is there any way around a writing a stored procedure for that? My current CTE works top down ( top > Expenses > 7-elven) and writes out "paths" to the leaves (ie "Expenses -> 7-Eleven" instead of just 7-Eleven) Svenne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cascading sum in tree with CTE?
to select a whole subtree of a particular node of a tree. u have to modify the tree representation in one of the two ways (according to you fine-tuned needs) (1) store in the EACH node the PATH from root to this node (2) store (l,r) segment representing the INCLUSIONS of nodes into other nodes subtree (exactly as segments include each other) (2) i forgot the "official" name of this type of tree representation. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Cascading sum in tree with CTE?
In article <4bbed49d.7080...@krap.dk>, Svenne Krap writes: > Hi . > My problem resembles this: > I have the following (simplified) tables > 1) create table account ( id serial, name varchar, parent_id int4 > references account, primary key (id)) > 2) create table transaction (id serial, account_id int4 references > account, memo varchar, debet, credit, primary key(id)) > So a basic ledger system, with a tree of accounts and transactions on > them. Some accounts have transactions others doesn't. > I have written a CTE which generates the tree, but I would like to > append to each line the sum of all debits and credits of this and all > sub accounts. > I think it sould be doable, but cannot bend my brain around it and my > google-fu has come out short. > Say the data was: > Account: > 1, 'Expenses', null > 2, 'IRS', 1 > 3, '7-Eleven' > Transaction: > 1, 2, 'Tax Jan 10', null, 100 > 2, 2, 'Tax Feb 10', null, 120 > 3, 2, 'Tax Feb 10 correction', 10,null > 4, 3, 'Sodas', 10, null > 5, 3, 'Beer', 5, null > I would expect a tree like: > 1, Debt, 25, 220 > 2, IRS, 10, 220 > 3, 7-eleven, 15, 0 > Is there any way around a writing a stored procedure for that? How about an ancestor table? WITH RECURSIVE tree (id, anc) AS ( SELECT id, id FROM account UNION ALL SELECT a.id, t.anc FROM account a JOIN tree t ON t.id = a.parent_id ) SELECT a.id, a.name, sum(x.debet) AS debet, sum(x.credit) AS credit FROM account a JOIN tree t ON t.anc = a.id LEFT JOIN transaction x ON x.account_id = t.id GROUP BY a.id, a.name -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] understanding select into
Hi, I am reviewing a function written by some xTuple guys. What is interesting about it is it uses the "INTO" statement like select something into _p from sometable where somecriteria. The function contiunes and uses the data retreived _p.somefield_name And then the function ends. Ok my question: I also thought the select "into" created a real table. But after running the function the table does not exist. I see no where that a 'drop' is issued. In fact the function uses lot's of select into's like (_test, _r, etc..). So would some kind soul explain what is happening. Could it be that "_p" is drop automaticly when the function ends? Something to do with scope. Could it have something to do with the fact the function returns only an integer? And that causes the table to be drop. As you can see I'm lost here! Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] understanding select into
Johnf, I would think that the _p, _test, _r etc are local variables within the procedure/function and this is the way that the value (from the select) gets assigned to that local variable. -Original Message- From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On Behalf Of John Sent: Friday, April 09, 2010 12:19 PM To: pgsql-sql@postgresql.org Subject: [SQL] understanding select into Hi, I am reviewing a function written by some xTuple guys. What is interesting about it is it uses the "INTO" statement like select something into _p from sometable where somecriteria. The function contiunes and uses the data retreived _p.somefield_name And then the function ends. Ok my question: I also thought the select "into" created a real table. But after running the function the table does not exist. I see no where that a 'drop' is issued. In fact the function uses lot's of select into's like (_test, _r, etc..). So would some kind soul explain what is happening. Could it be that "_p" is drop automaticly when the function ends? Something to do with scope. Could it have something to do with the fact the function returns only an integer? And that causes the table to be drop. As you can see I'm lost here! Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] understanding select into
Hello 2010/4/9 John : > Hi, > I am reviewing a function written by some xTuple guys. What is interesting > about it is it uses the "INTO" statement like > > select something into _p from sometable where somecriteria. > > The function contiunes and uses the data retreived > _p.somefield_name > > And then the function ends. > > > Ok my question: > > I also thought the select "into" created a real table. But after running the > function the table does not exist. I see no where that a 'drop' is issued. > In fact the function uses lot's of select into's like (_test, _r, etc..). So > would some kind soul explain what is happening. > _p is record variable. See some lines before. There will be DECLARE part DECLARE p RECORD; There is plpgsql's SELECT INTO and SQL's SELECT INTO with little bit different syntax. First - target is list of variables or record variable, second - target is table. > Could it be that "_p" is drop automaticly when the function ends? Something > to do with scope. > _p is just variable regards Pavel Stehule > Could it have something to do with the fact the function returns only an > integer? And that causes the table to be drop. > > As you can see I'm lost here! > > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] understanding select into
Wow thanks to all that replied - you folks are correct. The "_p" and the others are vars. _p RECORD; I won't forget that one for a long time (however, I do drink :-)) Johnf On Friday 09 April 2010 10:32:51 am Pavel Stehule wrote: > Hello > > 2010/4/9 John : > > Hi, > > I am reviewing a function written by some xTuple guys. What is > > interesting about it is it uses the "INTO" statement like > > > > select something into _p from sometable where somecriteria. > > > > The function contiunes and uses the data retreived > > _p.somefield_name > > > > And then the function ends. > > > > > > Ok my question: > > > > I also thought the select "into" created a real table. But after running > > the function the table does not exist. I see no where that a 'drop' is > > issued. In fact the function uses lot's of select into's like (_test, _r, > > etc..). So would some kind soul explain what is happening. > > _p is record variable. See some lines before. There will be DECLARE part > > DECLARE p RECORD; > > There is plpgsql's SELECT INTO and SQL's SELECT INTO with little bit > different syntax. First - target is list of variables or record > variable, second - target is table. > > > Could it be that "_p" is drop automaticly when the function ends? > > Something to do with scope. > > _p is just variable > > regards > Pavel Stehule > > > Could it have something to do with the fact the function returns only an > > integer? And that causes the table to be drop. > > > > As you can see I'm lost here! > > > > > > Johnf -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
