Skip to content
Snippets Groups Projects
Select Git revision
  • benchmark-tools
  • postgres-lambda
  • master default
  • REL9_4_25
  • REL9_5_20
  • REL9_6_16
  • REL_10_11
  • REL_11_6
  • REL_12_1
  • REL_12_0
  • REL_12_RC1
  • REL_12_BETA4
  • REL9_4_24
  • REL9_5_19
  • REL9_6_15
  • REL_10_10
  • REL_11_5
  • REL_12_BETA3
  • REL9_4_23
  • REL9_5_18
  • REL9_6_14
  • REL_10_9
  • REL_11_4
23 results

hstore_plperl.sql

Blame
    • Peter Eisentraut's avatar
      cac76582
      Add transforms feature · cac76582
      Peter Eisentraut authored
      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
      cac76582
      History
      Add transforms feature
      Peter Eisentraut authored
      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
    hstore_plperl.sql 3.13 KiB
    CREATE EXTENSION hstore_plperl;
    CREATE EXTENSION hstore_plperlu;
    
    SELECT transforms.udt_schema, transforms.udt_name,
           routine_schema, routine_name,
           group_name, transform_type
    FROM information_schema.transforms JOIN information_schema.routines
         USING (specific_catalog, specific_schema, specific_name)
    ORDER BY 1, 2, 5, 6;
    
    
    -- test hstore -> perl
    CREATE FUNCTION test1(val hstore) RETURNS int
    LANGUAGE plperlu
    TRANSFORM FOR TYPE hstore
    AS $$
    use Data::Dumper;
    $Data::Dumper::Sortkeys = 1;
    elog(INFO, Dumper($_[0]));
    return scalar(keys %{$_[0]});
    $$;
    
    SELECT test1('aa=>bb, cc=>NULL'::hstore);
    
    CREATE FUNCTION test1none(val hstore) RETURNS int
    LANGUAGE plperlu
    AS $$
    use Data::Dumper;
    $Data::Dumper::Sortkeys = 1;
    elog(INFO, Dumper($_[0]));
    return scalar(keys %{$_[0]});
    $$;
    
    SELECT test1none('aa=>bb, cc=>NULL'::hstore);
    
    CREATE FUNCTION test1list(val hstore) RETURNS int
    LANGUAGE plperlu
    TRANSFORM FOR TYPE hstore
    AS $$
    use Data::Dumper;
    $Data::Dumper::Sortkeys = 1;
    elog(INFO, Dumper($_[0]));
    return scalar(keys %{$_[0]});
    $$;
    
    SELECT test1list('aa=>bb, cc=>NULL'::hstore);
    
    
    -- test hstore[] -> perl
    CREATE FUNCTION test1arr(val hstore[]) RETURNS int
    LANGUAGE plperlu
    TRANSFORM FOR TYPE hstore
    AS $$
    use Data::Dumper;
    $Data::Dumper::Sortkeys = 1;
    elog(INFO, Dumper($_[0]->[0], $_[0]->[1]));
    return scalar(keys %{$_[0]});
    $$;
    
    SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
    
    
    -- test perl -> hstore
    CREATE FUNCTION test2() RETURNS hstore
    LANGUAGE plperl
    TRANSFORM FOR TYPE hstore
    AS $$
    $val = {a => 1, b => 'boo', c => undef};
    return $val;
    $$;
    
    SELECT test2();
    
    
    -- test perl -> hstore[]
    CREATE FUNCTION test2arr() RETURNS hstore[]
    LANGUAGE plperl
    TRANSFORM FOR TYPE hstore
    AS $$
    $val = [{a => 1, b => 'boo', c => undef}, {d => 2}];
    return $val;
    $$;
    
    SELECT test2arr();
    
    
    -- test as part of prepare/execute
    CREATE FUNCTION test3() RETURNS void
    LANGUAGE plperlu
    TRANSFORM FOR TYPE hstore
    AS $$
    use Data::Dumper;
    $Data::Dumper::Sortkeys = 1;
    
    $rv = spi_exec_query(q{SELECT 'aa=>bb, cc=>NULL'::hstore AS col1});
    elog(INFO, Dumper($rv->{rows}[0]->{col1}));
    
    $val = {a => 1, b => 'boo', c => undef};
    $plan = spi_prepare(q{SELECT $1::text AS col1}, "hstore");
    $rv = spi_exec_prepared($plan, {}, $val);
    elog(INFO, Dumper($rv->{rows}[0]->{col1}));
    $$;
    
    SELECT test3();
    
    
    -- test trigger
    CREATE TABLE test1 (a int, b hstore);
    INSERT INTO test1 VALUES (1, 'aa=>bb, cc=>NULL');
    SELECT * FROM test1;
    
    CREATE FUNCTION test4() RETURNS trigger
    LANGUAGE plperlu
    TRANSFORM FOR TYPE hstore
    AS $$
    use Data::Dumper;
    $Data::Dumper::Sortkeys = 1;
    elog(INFO, Dumper($_TD->{new}));
    if ($_TD->{new}{a} == 1) {
        $_TD->{new}{b} = {a => 1, b => 'boo', c => undef};
    }
    
    return "MODIFY";
    $$;
    
    CREATE TRIGGER test4 BEFORE UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE test4();
    
    UPDATE test1 SET a = a;
    SELECT * FROM test1;
    
    
    DROP TABLE test1;
    
    DROP FUNCTION test1(hstore);
    DROP FUNCTION test1none(hstore);
    DROP FUNCTION test1list(hstore);
    DROP FUNCTION test1arr(hstore[]);
    DROP FUNCTION test2();
    DROP FUNCTION test2arr();
    DROP FUNCTION test3();
    DROP FUNCTION test4();
    
    
    DROP EXTENSION hstore_plperl;
    DROP EXTENSION hstore_plperlu;
    DROP EXTENSION hstore;
    DROP EXTENSION plperl;
    DROP EXTENSION plperlu;