2010/12/28 Dmitriy Igrishin <dmitigr@xxxxxxxxx>
2010/12/28 Alexander Farber <alexander.farber@xxxxxxxxx>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, ';');
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
--
// Dmitriy.
