1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
|
CREATE EXTENSION hstore_plperlu CASCADE;
NOTICE: installing required extension "hstore"
NOTICE: installing required extension "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;
udt_schema | udt_name | routine_schema | routine_name | group_name | transform_type
------------+----------+----------------+-------------------+------------+----------------
public | hstore | public | hstore_to_plperlu | plperlu | FROM SQL
public | hstore | public | plperlu_to_hstore | plperlu | TO SQL
(2 rows)
-- test hstore -> perl
CREATE FUNCTION test1(val hstore) RETURNS int
LANGUAGE plperlu
TRANSFORM FOR TYPE hstore
AS $$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Indent = 0;
elog(INFO, Dumper($_[0]));
return scalar(keys %{$_[0]});
$$;
SELECT test1('aa=>bb, cc=>NULL'::hstore);
INFO: $VAR1 = {'aa' => 'bb','cc' => undef};
test1
-------
2
(1 row)
CREATE FUNCTION test1none(val hstore) RETURNS int
LANGUAGE plperlu
AS $$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Indent = 0;
elog(INFO, Dumper($_[0]));
return scalar(keys %{$_[0]});
$$;
SELECT test1none('aa=>bb, cc=>NULL'::hstore);
INFO: $VAR1 = '"aa"=>"bb", "cc"=>NULL';
test1none
-----------
0
(1 row)
CREATE FUNCTION test1list(val hstore) RETURNS int
LANGUAGE plperlu
TRANSFORM FOR TYPE hstore
AS $$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Indent = 0;
elog(INFO, Dumper($_[0]));
return scalar(keys %{$_[0]});
$$;
SELECT test1list('aa=>bb, cc=>NULL'::hstore);
INFO: $VAR1 = {'aa' => 'bb','cc' => undef};
test1list
-----------
2
(1 row)
-- test hstore[] -> perl
CREATE FUNCTION test1arr(val hstore[]) RETURNS int
LANGUAGE plperlu
TRANSFORM FOR TYPE hstore
AS $$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Indent = 0;
elog(INFO, Dumper($_[0]->[0], $_[0]->[1]));
return scalar(keys %{$_[0]});
$$;
SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
INFO: $VAR1 = {'aa' => 'bb','cc' => undef};$VAR2 = {'dd' => 'ee'};
test1arr
----------
2
(1 row)
-- 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;
$Data::Dumper::Indent = 0;
$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();
INFO: $VAR1 = {'aa' => 'bb','cc' => undef};
INFO: $VAR1 = '"a"=>"1", "b"=>"boo", "c"=>NULL';
test3
-------
(1 row)
-- test trigger
CREATE TABLE test1 (a int, b hstore);
INSERT INTO test1 VALUES (1, 'aa=>bb, cc=>NULL');
SELECT * FROM test1;
a | b
---+------------------------
1 | "aa"=>"bb", "cc"=>NULL
(1 row)
CREATE FUNCTION test4() RETURNS trigger
LANGUAGE plperlu
TRANSFORM FOR TYPE hstore
AS $$
use Data::Dumper;
$Data::Dumper::Sortkeys = 1;
$Data::Dumper::Indent = 0;
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;
INFO: $VAR1 = {'a' => '1','b' => {'aa' => 'bb','cc' => undef}};
SELECT * FROM test1;
a | b
---+---------------------------------
1 | "a"=>"1", "b"=>"boo", "c"=>NULL
(1 row)
DROP TABLE test1;
DROP FUNCTION test1(hstore);
DROP FUNCTION test1none(hstore);
DROP FUNCTION test1list(hstore);
DROP FUNCTION test1arr(hstore[]);
DROP FUNCTION test3();
DROP FUNCTION test4();
DROP EXTENSION hstore_plperlu;
DROP EXTENSION hstore;
DROP EXTENSION plperlu;
|
