May 3rd, 2015 by depesz | Tags: , , , , , , , | Comments Off on Waiting for 9.5 – Add transforms feature
Did it help? If yes - maybe you can help me?

On 26th of April, Peter Eisentraut committed patch:

Add transforms feature
 
This provides a mechanism for specifying conversions between SQL data
types and procedural languages.  As examples, there are transforms
for hstore and ltree for PL/Perl and PL/Python.
 
reviews by Pavel Stěhule and Andres Freund

After this was committed, there were subsequent patches that modified how it works, but the general idea is that we now have transforms, that work.

But what is this? Why?

Consider this, running in PostgreSQL 9.4.1:

CREATE function test_hstore (hstore) RETURNS void as $$
use Data::Dumper;
my $input = shift;
elog( NOTICE, Dumper($input) );
return;
$$ language plperlu;

In case you're not familiar with Perl – Dumper shows full value of given variable, in case of complex variables (arrays, hashes), it prints everything inside, recursively.

And how it works with hstore values? Like this:

$ select test_hstore( hstore( array[ 'key1', 'key2', 'key3' ], array[ 'value1', 'value2', 'value3' ]) );
NOTICE:  $VAR1 = '"key1"=>"value1", "key2"=>"value2", "key3"=>"value3"';

As you can see the value is printed as simple string, without any understanding. That's because this is what perl code in the functions gets.

In 9.5, with these new patches for transforms, result is very much different. To get it to work, I have to load another extension (hstore_plperlu), and then modify the source code a bit:

CREATE function test_hstore (hstore) RETURNS void TRANSFORM FOR TYPE hstore as $$
use Data::Dumper;
my $input = shift;
elog( NOTICE, Dumper($input) );
return;
$$ language plperlu;

added part is “TRANSFORM FOR TYPE hstore".

Now, when I call this functions, I get:

$ select test_hstore( hstore( array[ 'key1', 'key2', 'key3' ], array[ 'value1', 'value2', 'value3' ]) );
NOTICE:  $VAR1 = {
          'key2' => 'value2',
          'key3' => 'value3',
          'key1' => 'value1'
        };

Of course, it works the other way around too, I can make perl hash, and it will get transformed to proper hstore on return:

CREATE function test_hstore () RETURNS hstore TRANSFORM FOR TYPE hstore as $$
use Data::Dumper;
my $ret = {};
my $key = 'a';
$ret->{$key++} = $_ for 1..4;
elog( NOTICE, Dumper($ret) );
return $ret;
$$ language plperlu;
SELECT test_hstore();
psql:z.sql:10: NOTICE:  $VAR1 = {
          'b' => 2,
          'c' => 3,
          'a' => 1,
          'd' => 4
        };
              test_hstore               
----------------------------------------
 "a"=>"1", "b"=>"2", "c"=>"3", "d"=>"4"
(1 row)

As you can see in the NOTICE line, perl sees the value as proper hash, but upon returning it gets converted to hstore.

Similar things can be done for other datatypes (in plpythonu transformation for ltree conversts ltree to/from array).

Nice, it will definitely make writing certain functions much easier. Thanks.

Sorry, comments for this post are disabled.