CODEWITHSUNDEEP
postgresqlplpgsql
Alexander Farber
Alexander Farber

Reputation: 23018

PL/pgSQL: concatenating row values to a JSON-like string

I'm trying to retrieve player statistics for the last 20 weeks:

# select yw, money
from pref_money where id='OK122471020773'
order by yw desc limit 20;
   yw    | money
---------+-------
 2010-52 |  1130
 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)

But I would like to have the result as a string, where all values are concatenated by colons and semicolons like this:

"2010-44:185;2010-45:1110; .... ;2010-52:1130"

So I'm trying to create the following PL/pgSQL procedure:

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 := row.id || ':' || row.money || ';' stats;
end loop;

return stats;
end;
$BODY$ language plpgsql;

But I get the syntax error: 

ERROR:  syntax error at or near "for"
LINE 7:         for row in select yw, money from pref_money where id...

Using PostgreSQL 8.4.6 with CentOS 5.5 Linux.

UPDATE:

I'm trying to perform all this string concatenation with PL/pgSQL and not in PHP script, because I already have a main SQL select statement, which returns user information and that information is printed row by row as XML for my mobile app:

select u.id,
    u.first_name,
    u.female,
    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 ?

Here is the screenshot of the mobile app:

alt text

And here is an XML excerpt:

<?xml version="1.0"?>
<pref>
<user id="OK510352632290" name="ирина" money="2067" pos="1" medals="1" female="1" avatar="http://i221.odnoklassniki.ru/getImage?photoId=259607761026&amp;photoType=0" city="староконстантинов" />
<user id="OK19895063121" name="Александр" money="1912" pos="2" online="1" avatar="http://i69.odnoklassniki.ru/getImage?photoId=244173589553&amp;photoType=0" city="Сызрань" />
<user id="OK501875102516" name="Исмаил" money="1608" pos="3" online="1" avatar="http://i102.odnoklassniki.ru/res/stub_128x96.gif" city="Москва" />
.....
</pref>

But my problem is that I have 3 other tables, from which I need that statistics for the last 20 weeks. So I'm hoping to create 3 procedures returning varchars as in my original post and integrate them in this SQL select statement. So that I can add further attributes to the XML data:

<user id="OK12345" .... money_stats="2010-44:185;2010-45:1110; .... ;2010-52:1130" ..... />

Thank you! Alex

Upvotes: 1

Views: 3430

Answers (3)

rmcc
rmcc

Reputation: 783

You probably have already found the answer to your problem. Even so, the problem was indeed syntax.

The problem was that the declare statement was misplaced: it should appear before the begin (docs):

create or replace function pref_money_stats(_id varchar)
returns varchar as $BODY$
declare stats varchar;
begin
...

Another detail to take notice of is that you need to declare row as a record:

declare 
        stats varchar;
        row record;

Then this statement will run properly:

for row in select yw, money from pref_money where id=_id order by yw desc limit 20 loop

Upvotes: 1

nate c
nate c

Reputation: 9005

Aggregate functions are good for concatenating values:

create or replace function test
    (text, text, text)
    returns text as
    $$
        select $1 || ':' || $2 || ';' || $3
    $$
    language sql;

drop function test(text, text);
drop aggregate test(text, text) cascade;
create aggregate test(text, text)
(
    sfunc = test,
    stype = text,
    initcond = ''
);

test=# select test(a::text, b::text) from (select generate_series(1,3) as a, generate_series(4,5) a s b) t;

:1;4:2;5:3;4:1;5:2;4:3;5

(I'll leave it to you to deal with the leading colin :-)

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425683

This is not exactly JSON but pretty close:

SELECT  ARRAY
        (
        SELECT  ROW(yw, money)
        FROM    pref_money
        WHERE   id = 'OK122471020773'
        ORDER BY
                yw DESC
        LIMIT 20
        )::TEXT

This will output this string:

{"(2010-44:185)","(2010-45:1110)",…,"(2010-52:1130)"}

which can later be cast back into the appropriate types.

Upvotes: 0

Related Questions