2010/12/28 Alexander Farber <alexander.far...@gmail.com> > Hello, > > I'm working on a small app, which receives a list of 20 players in XML > format. > > The initial version works ok and I use there just 1 SQL statement and thus > it is easy for me to fetch results row by row and print XML at the same > time: > > select u.id, > u.first_name, > u.city, > u.avatar, > m.money, > u.login > u.logout as online > from pref_users u, pref_money m where > > m.yw=to_char(current_timestamp, 'YYYY-IW') and > u.id=m.id > order by m.money desc > limit 20 offset ? > > My problem is however, that I need to add more data for each user > representing their statistics over the last 20 weeks. > And that data is in separate tables: pref_money, pref_pass, pref_game: > > # select yw, money > from pref_money where id='OK122471020773' > order by yw desc limit 20; > yw | money > ---------+------- > 2010-52 | 760 > 2010-51 | 3848 > 2010-50 | 4238 > 2010-49 | 2494 > 2010-48 | 936 > 2010-47 | 3453 > 2010-46 | 3923 > 2010-45 | 1110 > 2010-44 | 185 > (9 rows) > SELECT string_agg(yw::text || money::text, ';');
> > For example for the table above I'd like to concatenate > those rows and add them as an XML attribute for that user: > > <user id="OK122471020773" first_name="..." city="..." ... > pref_money="2010-52:760;2010-51:3848;2010-50:4238;...." /> > > so that I can take that attribute in my app and use it in a chart. > > My problem is that I don't know how to bring this together > in 1 SQL statement (i.e. the SQL statement at the top and > then the concatenated 20 rows from 3 tables). > > Is it possible? Maybe I need to write a PgPlSQL > procedure for each of the 3 tables and then add them > to the SQL statement above? But how do I concatenate > the rows, should I create a PgPlSQL variable and always > append values to it in a loop or is there a better way? > > Thank you for any hints > Alex > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- // Dmitriy.
