Search Postgresql Archives

Re: JSON to INT[] or other custom type

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11/06/17, Bruno Wolff III (bruno@xxxxxxxx) wrote:
> On Sun, Jun 11, 2017 at 22:35:14 +0100,
>  Rory Campbell-Lange <rory@xxxxxxxxxxxxxxxxxx> wrote:
> >
> >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.
> 
> Does this example help?
> 
> area=> select (a->>0)::int, (a->>1)::int from json_array_elements('[[0, 1], [1, 2]]') as s(a);
> int4 | int4 ------+------
>    0 |    1
>    1 |    2
> (2 rows)

Hi Bruno

That worked great, thank you very much for the pointer.
I completely failed to see the top of the docs at
https://www.postgresql.org/docs/9.5/static/functions-json.html
-- my apologies.

I've put a working function and caller from PHP, Python below.

Many thanks
Rory

/* plpgsql test function */
CREATE OR REPLACE FUNCTION fn_test05 (
    num INT
    ,ds json
) RETURNS SETOF dow_session AS $$
DECLARE
    r test.dow_session;
BEGIN
    RAISE NOTICE 'num: %', num;

    FOR r IN 
        SELECT
            (n->>0)::int -- first json element
            ,(n->>1)::int
        FROM
            json_array_elements(ds)
        AS x(n)
    LOOP
        RETURN NEXT r;
    END LOOP;
    RETURN;
END;
$$ LANGUAGE plpgsql;


## python example
import psycopg2
import json
dbc = psycopg2.connect(database=<db>, user=<user>, password=<pass>)
cur = dbc.cursor()
query = 'select * from test.fn_test05(%s, %s)'
a_of_a = json.dumps([(0,2), (3,1), (5,2)])
qargs = (5, a_of_a)
cur.execute(query, qargs)
rs = cur.fetchall()
for r in rs:
    print r
# (0, 2)
# (3, 1)
# (5, 2)


## php example
<?php
$a = 77;
$b = array(array(0,1), array(2,3));
$j = json_encode($b);
$dbconn = pg_connect("dbname=<db> user=<user> password=<pass>");
$query = 'select * from test.fn_test05($1, $2)';
$result = pg_query_params($dbconn, $query, array($a, $j));
print_r(pg_fetch_all($result));
# Array
# (
#     [0] => Array
#         (
#             [dow] => 0
#             [session] => 1
#         )
# 
#     [1] => Array
#         (
#             [dow] => 2
#             [session] => 3
#         )
# )
?>



-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux