Concatenating several rows with a semicolon

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, ';');

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.

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:

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

Well, JSON might give you smaller size. Why you need XML then? :-)

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.

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:

<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

:-) 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="..."/>
...

:-)

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.