Fwd: row_to_json() with numerical indices in stead of associative indices
Hello there,
Is it possible to add an parameter for the function row_to_json()? So
you can choose for:
numerical indices: [1, true, "string", null]
associative indices: ["f1":1,"f2": true, "f3":"string","f4": null]
The this extra parameter can save bandwidth between the
webserver(postgresql/php) and client(javascript).
Now i'm doing it like this:
CREATE FUNCTION to_num_json(_in json) RETURNS json AS $$
DECLARE
_pair RECORD;
_arr json[];
BEGIN
FOR _pair IN SELECT * FROM json_each(_in) LOOP
_arr := _arr || _pair.value;
END LOOP;
RETURN to_json(_arr);
END
$$ language plpgsql;
SELECT to_num_json(
row_to_json((5, TRUE, 'string', NULL))
);
greetings
Tjibbe
Import Notes
Reply to msg id not found: CAF_dx13RjZ=KFaWsU8EHmHboTF3DbkYoTd3SpwSo_e58uBxu+A@mail.gmail.comReference msg id not found: CAF_dx10d=kmUvyuB_JGYw1J3R_gUeRBkGMASYW+PnMc4Z44V2w@mail.gmail.comReference msg id not found: CAF_dx13RjZ=KFaWsU8EHmHboTF3DbkYoTd3SpwSo_e58uBxu+A@mail.gmail.com
On Sat, Nov 30, 2013 at 11:48 PM, Tjibbe <tjibbe@rijpma.org> wrote:
Hello there,
Is it possible to add an parameter for the function row_to_json()? So
you can choose for:numerical indices: [1, true, "string", null]
associative indices: ["f1":1,"f2": true, "f3":"string","f4": null]The this extra parameter can save bandwidth between the
webserver(postgresql/php) and client(javascript).Now i'm doing it like this:
CREATE FUNCTION to_num_json(_in json) RETURNS json AS $$
DECLARE
_pair RECORD;
_arr json[];
BEGIN
FOR _pair IN SELECT * FROM json_each(_in) LOOP
_arr := _arr || _pair.value;
END LOOP;
RETURN to_json(_arr);
END
$$ language plpgsql;SELECT to_num_json(
row_to_json((5, TRUE, 'string', NULL))
);
You could rename your function to_num_json to row_to_json and trick
search_path to include it in your client depending on the schema where
the new function is as two functions with the same name but different
arguments can live together. IMO, you are right to do that with
plpgsql and json_each.
Regards,
--
Michael
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I had tried something similar, but it is not possible because:
ERROR: PL/pgSQL functions cannot accept type record
So it would be nice that in version postgresql 9.4 this parameter is added
to the function row_to_json.
Maybe RECORD as INPUT parameter is allowed in other procedural languages
like PL/phyton.
--
+31 6 29401726
tjibbe@rijpma.org
Jacobusstraat 185
3012 JM Rotterdam
On 1 December 2013 06:43, Michael Paquier <michael.paquier@gmail.com> wrote:
Show quoted text
On Sat, Nov 30, 2013 at 11:48 PM, Tjibbe <tjibbe@rijpma.org> wrote:
Hello there,
Is it possible to add an parameter for the function row_to_json()? So
you can choose for:numerical indices: [1, true, "string", null]
associative indices: ["f1":1,"f2": true, "f3":"string","f4": null]The this extra parameter can save bandwidth between the
webserver(postgresql/php) and client(javascript).Now i'm doing it like this:
CREATE FUNCTION to_num_json(_in json) RETURNS json AS $$
DECLARE
_pair RECORD;
_arr json[];
BEGIN
FOR _pair IN SELECT * FROM json_each(_in) LOOP
_arr := _arr || _pair.value;
END LOOP;
RETURN to_json(_arr);
END
$$ language plpgsql;SELECT to_num_json(
row_to_json((5, TRUE, 'string', NULL))
);You could rename your function to_num_json to row_to_json and trick
search_path to include it in your client depending on the schema where
the new function is as two functions with the same name but different
arguments can live together. IMO, you are right to do that with
plpgsql and json_each.Regards,
--
Michael