JSON to INT[] or other custom type

From: Rory Campbell-Lange <rory(at)campbell-lange(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: JSON to INT[] or other custom type
Date: 2017-06-11 21:35:14
Message-ID: 20170611213514.GB7156@campbell-lange.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm playing with plpgsql function parameters to try and come up with a
neat way of sending an array of arrays or array of custom types to
postgres from python and PHP.

Psycopg works fine with an array of custom types:

In [108]: query = 'select * from fn_test03(%s, %s::dow_session[])'
In [109]: qargs = (5, [(0,2), (1, 3)])
In [110]: cur.execute(query, qargs)
In [111]: rs = cur.fetchall() # function plays back looped arrays
In [112]: rs
Out[112]: [(0, 2), (1, 3)]

However PHP's Postgresql support isn't very array friendly as far as I
can see, and requires some horrible manual escaping.

Consequently I wondered if I could serialise the array structure into
json and send that to the postgresql function. PHP seems to handle that
fine. My problem therefore move to handling the json in the plpgsql
function.

I'm hoping, in the plpgsql function, to unfurl the supplied json into a
custom type or at least an array of ints, and I can't work out how to do
that.

select * from json_array_elements_text('[[0, 1], [1, 2]]');
value
--------
[0, 1]
[1, 2]
(2 rows)

works fine, but I can't seem to turn those values into actual ints or
anything else for that matter, apart from text via the
json_array_elements_text() function.

=> select n::integer[]
from
json_array_elements_text('[[0, 1], [1, 2]]') as x(n);

ERROR: malformed array literal: "[0, 1]"
DETAIL: Missing "]" after array dimensions.

=> select n::text[]::integer[]
from
json_array_elements_text('[[0, 1], [1, 2]]') as x(n);

ERROR: malformed array literal: "[0, 1]"
DETAIL: Missing "]" after array dimensions.

Thoughts gratefully received;
Rory

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Lucas Possamai 2017-06-11 21:39:43 Re: Huge Pages - setting the right value
Previous Message Paul Jungwirth 2017-06-11 20:55:54 Re: trying to program in PostgreSQL C a statistics function