Strange SQL result - any ideas.

[Paul Linehan <linehanp@xxxxxx>]

<CREATE TABLE and INSERT INTO statements at end of post>

I have a table (fred) that I want to transform into JSON and
I use the following command (ignore the backslash stuff):

SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g')
FROM
(
  SELECT * FROM fred
  ORDER BY mary, jimmy, paulie
) AS t;

which gives

                    regexp_replace
------------------------------------------------------
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
(5 rows)


which is fine (note that the field "mary" is sorted correctly) but
I want "proper" JSON - i.e. with open and close square brackets
i.e. ([ - ]) before and after the fields!

So, I tried this query:

SELECT '[' AS my_data  -- <<-- added line
UNION                          -- <<-- added line
SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g')
FROM
(
  SELECT * FROM fred
  ORDER BY mary, jimmy, paulie
) AS t
UNION                           -- <<-- added line
SELECT ']';                    -- <<-- added line

*_BUT_*, this gives


                       my_data
------------------------------------------------------
 ]
 [
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
(7 rows)


Two problems with this  result - one is that my square brackets are not in
the right place - this at least I understand - the first character of
each line is sorted by its ASCII value - '[' comes before ']' (naturally)
and '{' comes after them both - or have I got that right?

But, I do *_not_* understand why my table data is now out
of sort order - I've looked at it and can't see *_how_* the sort
order in my table data has been determined.

Anybody got any logical explanations as to what's going on?

TIA & Rgs,


Paul...


-- CREATE TABLE and INSERT INTO statements.


CREATE TABLE fred (
    mary integer PRIMARY KEY,
    jimmy integer,
    paulie character varying(20)
);

INSERT INTO fred (mary, jimmy, paulie) VALUES (2, 43, 'asfasfasfd');
INSERT INTO fred (mary, jimmy, paulie) VALUES (3, 435, 'ererere');
INSERT INTO fred (mary, jimmy, paulie) VALUES (6, 43343, 'eresdfssfsfasfae');
INSERT INTO fred (mary, jimmy, paulie) VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred (mary, jimmy, paulie) VALUES (3, 44545, '\sdfs\\\sfs\\gf');


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general