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
|
CREATE EXTENSION hstore_plpython2u CASCADE;
NOTICE: installing required extension "plpython2u"
-- test hstore -> python
CREATE FUNCTION test1(val hstore) RETURNS int
LANGUAGE plpythonu
TRANSFORM FOR TYPE hstore
AS $$
assert isinstance(val, dict)
i = list(val.items())
i.sort()
plpy.info(i)
return len(val)
$$;
SELECT test1('aa=>bb, cc=>NULL'::hstore);
INFO: [('aa', 'bb'), ('cc', None)]
test1
-------
2
(1 row)
-- the same with the versioned language name
CREATE FUNCTION test1n(val hstore) RETURNS int
LANGUAGE plpython2u
TRANSFORM FOR TYPE hstore
AS $$
assert isinstance(val, dict)
i = list(val.items())
i.sort()
plpy.info(i)
return len(val)
$$;
SELECT test1n('aa=>bb, cc=>NULL'::hstore);
INFO: [('aa', 'bb'), ('cc', None)]
test1n
--------
2
(1 row)
-- test hstore[] -> python
CREATE FUNCTION test1arr(val hstore[]) RETURNS int
LANGUAGE plpythonu
TRANSFORM FOR TYPE hstore
AS $$
assert(val == [{'aa': 'bb', 'cc': None}, {'dd': 'ee'}])
return len(val)
$$;
SELECT test1arr(array['aa=>bb, cc=>NULL'::hstore, 'dd=>ee']);
test1arr
----------
2
(1 row)
-- test python -> hstore
CREATE FUNCTION test2() RETURNS hstore
LANGUAGE plpythonu
TRANSFORM FOR TYPE hstore
AS $$
val = {'a': 1, 'b': 'boo', 'c': None}
return val
$$;
SELECT test2();
test2
---------------------------------
"a"=>"1", "b"=>"boo", "c"=>NULL
(1 row)
-- test python -> hstore[]
CREATE FUNCTION test2arr() RETURNS hstore[]
LANGUAGE plpythonu
TRANSFORM FOR TYPE hstore
AS $$
val = [{'a': 1, 'b': 'boo', 'c': None}, {'d': 2}]
return val
$$;
SELECT test2arr();
test2arr
--------------------------------------------------------------
{"\"a\"=>\"1\", \"b\"=>\"boo\", \"c\"=>NULL","\"d\"=>\"2\""}
(1 row)
-- test as part of prepare/execute
CREATE FUNCTION test3() RETURNS void
LANGUAGE plpythonu
TRANSFORM FOR TYPE hstore
AS $$
rv = plpy.execute("SELECT 'aa=>bb, cc=>NULL'::hstore AS col1")
assert(rv[0]["col1"] == {'aa': 'bb', 'cc': None})
val = {'a': 1, 'b': 'boo', 'c': None}
plan = plpy.prepare("SELECT $1::text AS col1", ["hstore"])
rv = plpy.execute(plan, [val])
assert(rv[0]["col1"] == '"a"=>"1", "b"=>"boo", "c"=>NULL')
$$;
SELECT test3();
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 plpythonu
TRANSFORM FOR TYPE hstore
AS $$
assert(TD["new"] == {'a': 1, 'b': {'aa': 'bb', 'cc': None}})
if TD["new"]["a"] == 1:
TD["new"]["b"] = {'a': 1, 'b': 'boo', 'c': None}
return "MODIFY"
$$;
CREATE TRIGGER test4 BEFORE UPDATE ON test1 FOR EACH ROW EXECUTE PROCEDURE test4();
UPDATE test1 SET a = a;
SELECT * FROM test1;
a | b
---+---------------------------------
1 | "a"=>"1", "b"=>"boo", "c"=>NULL
(1 row)
|
