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
|
CREATE EXTENSION hstore_plpython3u CASCADE;
-- test hstore -> python
CREATE FUNCTION test1(val hstore) RETURNS int
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
assert isinstance(val, dict)
plpy.info(sorted(val.items()))
return len(val)
$$;
SELECT test1('aa=>bb, cc=>NULL'::hstore);
-- the same with the versioned language name
CREATE FUNCTION test1n(val hstore) RETURNS int
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
assert isinstance(val, dict)
plpy.info(sorted(val.items()))
return len(val)
$$;
SELECT test1n('aa=>bb, cc=>NULL'::hstore);
-- test that a non-mapping result is correctly rejected
CREATE FUNCTION test1bad() RETURNS hstore
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
return "foo"
$$;
SELECT test1bad();
-- test hstore[] -> python
CREATE FUNCTION test1arr(val hstore[]) RETURNS int
LANGUAGE plpython3u
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']);
-- test python -> hstore
CREATE FUNCTION test2(a int, b text) RETURNS hstore
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
val = {'a': a, 'b': b, 'c': None}
return val
$$;
SELECT test2(1, 'boo');
--- test ruleutils
\sf test2
-- test python -> hstore[]
CREATE FUNCTION test2arr() RETURNS hstore[]
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
val = [{'a': 1, 'b': 'boo', 'c': None}, {'d': 2}]
return val
$$;
SELECT test2arr();
-- test python -> domain over hstore
CREATE DOMAIN hstore_foo AS hstore CHECK(VALUE ? 'foo');
CREATE FUNCTION test2dom(fn text) RETURNS hstore_foo
LANGUAGE plpython3u
TRANSFORM FOR TYPE hstore
AS $$
return {'a': 1, fn: 'boo', 'c': None}
$$;
SELECT test2dom('foo');
SELECT test2dom('bar'); -- fail
-- test as part of prepare/execute
CREATE FUNCTION test3() RETURNS void
LANGUAGE plpython3u
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();
-- 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 plpython3u
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;
|
