Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl

On 28th of March 2018, Peter Eisentraut committed patch:

Transforms for jsonb to PL/Python
 
Add a new contrib module jsonb_plpython that provide a transform between
jsonb and PL/Python.  jsonb values are converted to appropriate Python
types such as dicts and lists, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

and then, on 3rd of April 2018, he also committed patch:

Transforms for jsonb to PL/Perl
 
Add a new contrib module jsonb_plperl that provides a transform between
jsonb and PL/Perl.  jsonb values are converted to appropriate Perl types
such as arrays and hashes, and vice versa.
 
Author: Anthony Bykov <a.bykov@postgrespro.ru>

Well, I don't really write in Python, but I can show you what this is about in Perl.

Before, for example in PostgreSQL 10, when you passed jsonb to PL/Perl function:

CREATE OR REPLACE FUNCTION testit(jsonb) RETURNS void AS $$
my $arg = shift;
elog(NOTICE, "Arg is: [$arg]");
elog(NOTICE, "Arg ref is: [" . REF($arg) . "]");
elog(NOTICE, "Arg len is: [" . LENGTH($arg) . "]");
$$ LANGUAGE plperl;
 
SELECT testit( '{ "id": 1, "name": "A green door", "price": 12.50, "tags": ["home", "green"] }'::jsonb );
psql:test.sql:11: NOTICE:  Arg IS: [{"id": 1, "name": "A green door", "tags": ["home", "green"], "price": 12.50}]
psql:test.sql:11: NOTICE:  Arg REF IS: []
psql:test.sql:11: NOTICE:  Arg len IS: [76]
 testit
--------
 
(1 ROW)

The value was passed as simple string.

But now, in Pg11:

CREATE OR REPLACE FUNCTION testit(jsonb)
    RETURNS void
    TRANSFORM FOR TYPE jsonb
    LANGUAGE plperl
AS $$
my $arg = shift;
elog(NOTICE, "Arg is: [$arg]");
elog(NOTICE, "Arg ref is: [" . REF($arg) . "]");
elog(NOTICE, "Arg len is: [" . LENGTH($arg) . "]");
$$;
 
SELECT testit( '{ "id": 1, "name": "A green door", "price": 12.50, "tags": ["home", "green"] }'::jsonb );
psql:test.sql:15: NOTICE:  Arg IS: [HASH(0x558375ceb000)]
psql:test.sql:15: NOTICE:  Arg REF IS: [HASH]
psql:test.sql:15: NOTICE:  Arg len IS: [20]
 testit 
--------
 
(1 ROW)

Function definition needs “TRANSFORM" clause, but aside from this – it's brilliant. Having the value parsed into proper hash means we can easily scan it, modify and do whatever we need or want.

That's a great addition. Thanks a lot guys 🙂

One thought on “Waiting for PostgreSQL 11 – Transforms for jsonb to PL/Python and to PL/Perl”

  1. Thanks for writing this up! Here is an example with Python 2:

    CREATE LANGUAGE plpython2u;
 
/** Pre-11 */
CREATE OR REPLACE FUNCTION testit(d jsonb)
    RETURNS void
    LANGUAGE plpython2u
AS $$
    print d
    print TYPE(d)
    print len(d)
$$;
 
/** Post-11 */
CREATE EXTENSION jsonb_plpython2u CASCADE;
CREATE OR REPLACE FUNCTION testit(d jsonb)
    RETURNS void
    TRANSFORM FOR TYPE jsonb
    LANGUAGE plpython2u
AS $$
    print d
    print TYPE(d)
    print len(d)
$$;

Comments are closed.