Thread: 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;

> -----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

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

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