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
152
153
154
155
156
157
158
159
160
161
162
163
|
--
-- Test returning tuples
--
CREATE TABLE table_record (
first text,
second int4
) ;
CREATE TYPE type_record AS (
first text,
second int4
) ;
CREATE FUNCTION test_table_record_as(typ text, first text, second integer, retnull boolean) RETURNS table_record AS $$
if retnull:
return None
if typ == 'dict':
return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
elif typ == 'tuple':
return ( first, second )
elif typ == 'list':
return [ first, second ]
elif typ == 'obj':
class type_record: pass
type_record.first = first
type_record.second = second
return type_record
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_type_record_as(typ text, first text, second integer, retnull boolean) RETURNS type_record AS $$
if retnull:
return None
if typ == 'dict':
return { 'first': first, 'second': second, 'additionalfield': 'must not cause trouble' }
elif typ == 'tuple':
return ( first, second )
elif typ == 'list':
return [ first, second ]
elif typ == 'obj':
class type_record: pass
type_record.first = first
type_record.second = second
return type_record
elif typ == 'str':
return "('%s',%r)" % (first, second)
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_in_out_params(first in text, second out text) AS $$
return first + '_in_to_out';
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_in_out_params_multi(first in text,
second out text, third out text) AS $$
return (first + '_record_in_to_out_1', first + '_record_in_to_out_2');
$$ LANGUAGE plpython3u;
CREATE FUNCTION test_inout_params(first inout text) AS $$
return first + '_inout';
$$ LANGUAGE plpython3u;
-- Test tuple returning functions
SELECT * FROM test_table_record_as('dict', null, null, false);
SELECT * FROM test_table_record_as('dict', 'one', null, false);
SELECT * FROM test_table_record_as('dict', null, 2, false);
SELECT * FROM test_table_record_as('dict', 'three', 3, false);
SELECT * FROM test_table_record_as('dict', null, null, true);
SELECT * FROM test_table_record_as('tuple', null, null, false);
SELECT * FROM test_table_record_as('tuple', 'one', null, false);
SELECT * FROM test_table_record_as('tuple', null, 2, false);
SELECT * FROM test_table_record_as('tuple', 'three', 3, false);
SELECT * FROM test_table_record_as('tuple', null, null, true);
SELECT * FROM test_table_record_as('list', null, null, false);
SELECT * FROM test_table_record_as('list', 'one', null, false);
SELECT * FROM test_table_record_as('list', null, 2, false);
SELECT * FROM test_table_record_as('list', 'three', 3, false);
SELECT * FROM test_table_record_as('list', null, null, true);
SELECT * FROM test_table_record_as('obj', null, null, false);
SELECT * FROM test_table_record_as('obj', 'one', null, false);
SELECT * FROM test_table_record_as('obj', null, 2, false);
SELECT * FROM test_table_record_as('obj', 'three', 3, false);
SELECT * FROM test_table_record_as('obj', null, null, true);
SELECT * FROM test_type_record_as('dict', null, null, false);
SELECT * FROM test_type_record_as('dict', 'one', null, false);
SELECT * FROM test_type_record_as('dict', null, 2, false);
SELECT * FROM test_type_record_as('dict', 'three', 3, false);
SELECT * FROM test_type_record_as('dict', null, null, true);
SELECT * FROM test_type_record_as('tuple', null, null, false);
SELECT * FROM test_type_record_as('tuple', 'one', null, false);
SELECT * FROM test_type_record_as('tuple', null, 2, false);
SELECT * FROM test_type_record_as('tuple', 'three', 3, false);
SELECT * FROM test_type_record_as('tuple', null, null, true);
SELECT * FROM test_type_record_as('list', null, null, false);
SELECT * FROM test_type_record_as('list', 'one', null, false);
SELECT * FROM test_type_record_as('list', null, 2, false);
SELECT * FROM test_type_record_as('list', 'three', 3, false);
SELECT * FROM test_type_record_as('list', null, null, true);
SELECT * FROM test_type_record_as('obj', null, null, false);
SELECT * FROM test_type_record_as('obj', 'one', null, false);
SELECT * FROM test_type_record_as('obj', null, 2, false);
SELECT * FROM test_type_record_as('obj', 'three', 3, false);
SELECT * FROM test_type_record_as('obj', null, null, true);
SELECT * FROM test_type_record_as('str', 'one', 1, false);
SELECT * FROM test_in_out_params('test_in');
SELECT * FROM test_in_out_params_multi('test_in');
SELECT * FROM test_inout_params('test_in');
-- try changing the return types and call functions again
ALTER TABLE table_record DROP COLUMN first;
ALTER TABLE table_record DROP COLUMN second;
ALTER TABLE table_record ADD COLUMN first text;
ALTER TABLE table_record ADD COLUMN second int4;
SELECT * FROM test_table_record_as('obj', 'one', 1, false);
ALTER TYPE type_record DROP ATTRIBUTE first;
ALTER TYPE type_record DROP ATTRIBUTE second;
ALTER TYPE type_record ADD ATTRIBUTE first text;
ALTER TYPE type_record ADD ATTRIBUTE second int4;
SELECT * FROM test_type_record_as('obj', 'one', 1, false);
-- errors cases
CREATE FUNCTION test_type_record_error1() RETURNS type_record AS $$
return { 'first': 'first' }
$$ LANGUAGE plpython3u;
SELECT * FROM test_type_record_error1();
CREATE FUNCTION test_type_record_error2() RETURNS type_record AS $$
return [ 'first' ]
$$ LANGUAGE plpython3u;
SELECT * FROM test_type_record_error2();
CREATE FUNCTION test_type_record_error3() RETURNS type_record AS $$
class type_record: pass
type_record.first = 'first'
return type_record
$$ LANGUAGE plpython3u;
SELECT * FROM test_type_record_error3();
CREATE FUNCTION test_type_record_error4() RETURNS type_record AS $$
return 'foo'
$$ LANGUAGE plpython3u;
SELECT * FROM test_type_record_error4();
|
