Обсуждение: Concatenating several rows with a semicolon
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) 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
I'm trying: create or replace function pref_money_stats(_id varchar) returns varchar as $BODY$ begin declare stats varchar; for row in select yw, money from pref_money where id=_id order by yw desc limit 20 loop stats := stats || ";" || row.id || ":" || row.money; end loop; return stats; end; $BODY$ language plpgsql; but get the error: ERROR: syntax error at or near "for" LINE 7: for row in select yw, money from pref_money where id... ^ Regards Alex
Alex, > create or replace function pref_money_stats(_id varchar) > returns varchar as $BODY$ > begin > > declare stats varchar; > > for row in select yw, money from pref_money where id=_id order > by yw desc limit 20 loop > stats := stats || ";" || row.id || ":" || row.money; > end loop; > > return stats; > end; > $BODY$ language plpgsql; > > but get the error: > > ERROR: syntax error at or near "for" > LINE 7: for row in select yw, money from pref_money where id... Your declare statement should be before the begin statement. create or replace function pref_money_stats(_id varchar) returns varchar as $$ declare stats varchar; begin for row ... return stats; end; $$ language plpgsql;
2010/12/28 Alexander Farber <alexander.farber@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.
2010/12/28 Dmitriy Igrishin <dmitigr@gmail.com>
2010/12/28 Alexander Farber <alexander.farber@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, ';');
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.
> -----Original Message----- > From: Alexander Farber [mailto:alexander.farber@gmail.com] > Sent: Tuesday, December 28, 2010 10:33 AM > To: pgsql-general@postgresql.org > Subject: Concatenating several rows with a semicolon > > 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) > > 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 > Based on your PG version there are different solutions to your problem. Not to re-invent the wheel, check this article: http://www.postgresonline.com/journal/archives/191-String-Aggregation-in -PostgreSQL%2C-SQL-Server%2C-and-MySQL.html%23extended Regards, Igor Neyman
In response to "Igor Neyman" <ineyman@perceptron.com>: > > > > -----Original Message----- > > From: Alexander Farber [mailto:alexander.farber@gmail.com] > > Sent: Tuesday, December 28, 2010 10:33 AM > > To: pgsql-general@postgresql.org > > Subject: Concatenating several rows with a semicolon > > > > 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) > > > > 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 > > > > Based on your PG version there are different solutions to your problem. > Not to re-invent the wheel, check this article: > http://www.postgresonline.com/journal/archives/191-String-Aggregation-in-PostgreSQL%2C-SQL-Server%2C-and-MySQL.html%23extended This doesn't invalidate Igor's response, but you're using XML wrong. If there are multiple entries for pref_money, then each one should be a container inside user, i.e.: <user id="bla bla bla ...> <pref_money date="2010-52" money="760" /> <pref_money date="2010-51" money="3848" /> ... etc ... </user> But then again, it appears as if your yw field is a textual field being used to store a date, so I expect you have bigger problems coming down the pike. In all essence, you XML should probably look like this: <user id="bla bla bla ...> <pref_money year="2010" week="52" money="760" /> <pref_money year="2010" week="51" money="3848" /> ... etc ... </user> And that yw field should be replaced with a week_ending field that is a date type. You can extract that into year and week using date_part(). Just 15 years of DB experience making me antsy ... does this make me one of those people who freak out when someone says something wrong on a message board and just _HAS_ to correct them? -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Hello Bill and others, I don't agree about yw being a bad thing since I have weekly raings in my app, but your XML suggestion - On Tue, Dec 28, 2010 at 9:29 PM, Bill Moran <wmoran@potentialtech.com> wrote: > > If there are multiple entries for pref_money, then each one should be > a container inside user, i.e.: > > <user id="bla bla bla ...> > <pref_money date="2010-52" money="760" /> > <pref_money date="2010-51" money="3848" /> > ... etc ... > </user> is a very good point, thanks! Alex
2010/12/29 Alexander Farber <alexander.farber@gmail.com>
Hello Bill and others,
I don't agree about yw being a bad thing
since I have weekly raings in my app,
but your XML suggestion -
On Tue, Dec 28, 2010 at 9:29 PM, Bill Moran <wmoran@potentialtech.com> wrote:
>
> If there are multiple entries for pref_money, then each one should be
> a container inside user, i.e.:
>
> <user id="bla bla bla ...>
> <pref_money date="2010-52" money="760" />
> <pref_money date="2010-51" money="3848" />
> ... etc ...
> </user>
Well, generally storing data in attributes should be avoided:
<user id="id">
<pref_money>
<date>...</date>
<money>...</money>
</pref_money>
...
</user>
is a better.
<pref_money>
<date>...</date>
<money>...</money>
</pref_money>
...
</user>
is a better.
is a very good point, thanks!
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.
On Tue, Dec 28, 2010 at 10:31 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote: >> > <user id="bla bla bla ...> >> > <pref_money date="2010-52" money="760" /> >> > <pref_money date="2010-51" money="3848" /> >> > ... etc ... >> > </user> > > Well, generally storing data in attributes should be avoided: > > <user id="id"> > <pref_money> > <date>...</date> > <money>...</money> > </pref_money> > ... > </user> > is a better. Attributes give me smaller size... Regards Alex
2010/12/29 Alexander Farber <alexander.farber@gmail.com>
On Tue, Dec 28, 2010 at 10:31 PM, Dmitriy Igrishin <dmitigr@gmail.com> wrote:Attributes give me smaller size...
>> > <user id="bla bla bla ...>
>> > <pref_money date="2010-52" money="760" />
>> > <pref_money date="2010-51" money="3848" />
>> > ... etc ...
>> > </user>
>
> Well, generally storing data in attributes should be avoided:
>
> <user id="id">
> <pref_money>
> <date>...</date>
> <money>...</money>
> </pref_money>
> ...
> </user>
> is a better.
Well, JSON might give you smaller size. Why you need XML then? :-)
RegardsAlex
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
// Dmitriy.
In response to Alexander Farber <alexander.farber@gmail.com>: > Hello Bill and others, > > I don't agree about yw being a bad thing > since I have weekly raings in my app, > but your XML suggestion - Do as you like, but I'll bet my reputation that decision will become an unnecessary limitation for the application at some point in the future. At the least, you have completely crippled PostgreSQL's powerful date arithmetic abilities. You've also made it so that if you want to combine results to produce monthly, quarterly, or yearly reports, that you'll have some crazy regular expressioning going on. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/
Hello Dmitriy, I think this combination of attributes and children: >> >> > <user id="bla bla bla ...> >> >> > <pref_money date="2010-52" money="760" /> >> >> > <pref_money date="2010-51" money="3848" /> >> >> > ... etc ... >> >> > </user> will be a good balance between size and my original problem (combining user data and their stats in 1 chunk of information). And I don't use JSON, because it is not natively supported by Flex/Flash and my app is in Flex (here is its pic: http://stackoverflow.com/questions/4548878/pl-pgsql-concatenating-row-values-to-a-json-like-string ) >> > Well, generally storing data in attributes should be avoided: You haven't backuped your statement by any arguments Regards Alex
2010/12/29 Alexander Farber <alexander.farber@gmail.com>
Hello Dmitriy,
I think this combination of attributes and children:will be a good balance between size and my original problem
>> >> > <user id="bla bla bla ...>
>> >> > <pref_money date="2010-52" money="760" />
>> >> > <pref_money date="2010-51" money="3848" />
>> >> > ... etc ...
>> >> > </user>
(combining user data and their stats in 1 chunk of information).
And I don't use JSON, because it is not natively
supported by Flex/Flash and my app is in Flex (here is its pic:
http://stackoverflow.com/questions/4548878/pl-pgsql-concatenating-row-values-to-a-json-like-string
)You haven't backuped your statement by any arguments
>> > Well, generally storing data in attributes should be avoided:
:-) You have asked how to aggregate string -- I've answered you how
to do it by one statement without needs to write any of PL/pgSQL code.
So the string aggregation problem is solved. ;-)
This list is not correct place to discuss XML. My only argument is a
common sense. You don't make difference between the data and attributes.
The data of <pref_money> is obviously money amount and the date is
obviously its attribute:
<user id="id">
<pref_money date="2010-..">money_value</pref_money>
...
</user>
PS. If you don't want to follow this way you can "reduce" the size of XML
transfer by placing all the data in one tag:
<user id="id" prefmoneydate="2010-.." prefmoneyvalue="..."/>
...
:-)
to do it by one statement without needs to write any of PL/pgSQL code.
So the string aggregation problem is solved. ;-)
This list is not correct place to discuss XML. My only argument is a
common sense. You don't make difference between the data and attributes.
The data of <pref_money> is obviously money amount and the date is
obviously its attribute:
<user id="id">
<pref_money date="2010-..">money_value</pref_money>
...
</user>
PS. If you don't want to follow this way you can "reduce" the size of XML
transfer by placing all the data in one tag:
<user id="id" prefmoneydate="2010-.." prefmoneyvalue="..."/>
...
:-)
Regards
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.