Postgres Pro
Downloads
Home   >   mailing lists

Thread: Fwd: row_to_json() with numerical indices in stead of associative indices

Fwd: row_to_json() with numerical indices in stead of associative indices

From
Tjibbe
Date:
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

Re: Fwd: row_to_json() with numerical indices in stead of associative indices

From
Michael Paquier
Date:
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

Re: Fwd: row_to_json() with numerical indices in stead of associative indices

From
Tjibbe
Date:
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:
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