Re: Fwd: row_to_json() with numerical indices in stead of associative indices - Mailing list pgsql-general

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