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
