2010/12/28 Dmitriy Igrishin <dmit...@gmail.com> > > > 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, ';'); > Sorry, 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. > > > -- // Dmitriy.
