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

Question

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

nate c · Accepted Answer · 2010-12-28 21:24:43Z

1

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

answered Dec 28, 2010 at 21:24

nate c

9,0532 gold badges29 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lee Goddard · Accepted Answer · 2021-12-01 10:31:53Z

1

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

edited Dec 1, 2021 at 10:31

Lee Goddard

11.3k5 gold badges53 silver badges71 bronze badges

answered Apr 28, 2011 at 11:59

rmcc

7831 gold badge6 silver badges15 bronze badges

Comments

Quassnoi · Accepted Answer · 2010-12-28 19:39:59Z

0

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.

answered Dec 28, 2010 at 19:39

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

3 Comments

Alexander Farber Over a year ago

Thank you, but looks more like a cool hack to me, because contains the round brackets and quotes which I don't need in my receiving script. Any suggestions how to fix my procedure maybe?

Quassnoi Over a year ago

@Alexander: this is the PostgreSQL's native way to serialize records. It is of course possible to do a native JSON on PostgreSQL side, but, honestly, the string processing is what is better done in PHP which supports lambda expressions and json_encode. Why don't you return the set into the script and parse it there?

Alexander Farber Over a year ago

the reason for that is, that I'm trying to use this procedure inside of a select statement - to return player statistics for last 20 weeks. I've updated my question above

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related