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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
|
-- first some tests of basic functionality
CREATE EXTENSION plpython2u;
-- really stupid function just to get the module loaded
CREATE FUNCTION stupid() RETURNS text AS 'return "zarkon"' LANGUAGE plpythonu;
select stupid();
stupid
--------
zarkon
(1 row)
-- check 2/3 versioning
CREATE FUNCTION stupidn() RETURNS text AS 'return "zarkon"' LANGUAGE plpython2u;
select stupidn();
stupidn
---------
zarkon
(1 row)
-- test multiple arguments and odd characters in function name
CREATE FUNCTION "Argument test #1"(u users, a1 text, a2 text) RETURNS text
AS
'keys = list(u.keys())
keys.sort()
out = []
for key in keys:
out.append("%s: %s" % (key, u[key]))
words = a1 + " " + a2 + " => {" + ", ".join(out) + "}"
return words'
LANGUAGE plpythonu;
select "Argument test #1"(users, fname, lname) from users where lname = 'doe' order by 1;
Argument test #1
-----------------------------------------------------------------------
jane doe => {fname: jane, lname: doe, userid: 1, username: j_doe}
john doe => {fname: john, lname: doe, userid: 2, username: johnd}
willem doe => {fname: willem, lname: doe, userid: 3, username: w_doe}
(3 rows)
-- check module contents
CREATE FUNCTION module_contents() RETURNS text AS
$$
contents = list(filter(lambda x: not x.startswith("__"), dir(plpy)))
contents.sort()
return ", ".join(contents)
$$ LANGUAGE plpythonu;
select module_contents();
module_contents
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Error, Fatal, SPIError, cursor, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, spiexceptions, subtransaction, warning
(1 row)
CREATE FUNCTION elog_test_basic() RETURNS void
AS $$
plpy.debug('debug')
plpy.log('log')
plpy.info('info')
plpy.info(37)
plpy.info()
plpy.info('info', 37, [1, 2, 3])
plpy.notice('notice')
plpy.warning('warning')
plpy.error('error')
$$ LANGUAGE plpythonu;
SELECT elog_test_basic();
INFO: info
INFO: 37
INFO: ()
INFO: ('info', 37, [1, 2, 3])
NOTICE: notice
WARNING: warning
ERROR: plpy.Error: error
CONTEXT: Traceback (most recent call last):
PL/Python function "elog_test_basic", line 10, in <module>
plpy.error('error')
PL/Python function "elog_test_basic"
CREATE FUNCTION elog_test() RETURNS void
AS $$
plpy.debug('debug', detail = 'some detail')
plpy.log('log', detail = 'some detail')
plpy.info('info', detail = 'some detail')
plpy.info()
plpy.info('the question', detail = 42);
plpy.info('This is message text.',
detail = 'This is detail text',
hint = 'This is hint text.',
sqlstate = 'XX000',
schema = 'any info about schema',
table = 'any info about table',
column = 'any info about column',
datatype = 'any info about datatype',
constraint = 'any info about constraint')
plpy.notice('notice', detail = 'some detail')
plpy.warning('warning', detail = 'some detail')
plpy.error('stop on error', detail = 'some detail', hint = 'some hint')
$$ LANGUAGE plpythonu;
SELECT elog_test();
INFO: info
DETAIL: some detail
INFO: ()
INFO: the question
DETAIL: 42
INFO: This is message text.
DETAIL: This is detail text
HINT: This is hint text.
NOTICE: notice
DETAIL: some detail
WARNING: warning
DETAIL: some detail
ERROR: plpy.Error: stop on error
DETAIL: some detail
HINT: some hint
CONTEXT: Traceback (most recent call last):
PL/Python function "elog_test", line 18, in <module>
plpy.error('stop on error', detail = 'some detail', hint = 'some hint')
PL/Python function "elog_test"
do $$ plpy.info('other types', detail = (10,20)) $$ LANGUAGE plpythonu;
INFO: other types
DETAIL: (10, 20)
do $$
import time;
from datetime import date
plpy.info('other types', detail = date(2016,2,26))
$$ LANGUAGE plpythonu;
INFO: other types
DETAIL: 2016-02-26
do $$
basket = ['apple', 'orange', 'apple', 'pear', 'orange', 'banana']
plpy.info('other types', detail = basket)
$$ LANGUAGE plpythonu;
INFO: other types
DETAIL: ['apple', 'orange', 'apple', 'pear', 'orange', 'banana']
-- should fail
do $$ plpy.info('wrong sqlstate', sqlstate='54444A') $$ LANGUAGE plpythonu;
ERROR: invalid SQLSTATE code
CONTEXT: PL/Python anonymous code block
do $$ plpy.info('unsupported argument', blabla='fooboo') $$ LANGUAGE plpythonu;
ERROR: 'blabla' is an invalid keyword argument for this function
CONTEXT: PL/Python anonymous code block
do $$ plpy.info('first message', message='second message') $$ LANGUAGE plpythonu;
ERROR: the message is already specified
CONTEXT: PL/Python anonymous code block
do $$ plpy.info('first message', 'second message', message='third message') $$ LANGUAGE plpythonu;
ERROR: the message is already specified
CONTEXT: PL/Python anonymous code block
-- raise exception in python, handle exception in plgsql
CREATE OR REPLACE FUNCTION raise_exception(_message text, _detail text DEFAULT NULL, _hint text DEFAULT NULL,
_sqlstate text DEFAULT NULL,
_schema text DEFAULT NULL, _table text DEFAULT NULL, _column text DEFAULT NULL,
_datatype text DEFAULT NULL, _constraint text DEFAULT NULL)
RETURNS void AS $$
kwargs = { "message":_message, "detail":_detail, "hint":_hint,
"sqlstate":_sqlstate, "schema":_schema, "table":_table,
"column":_column, "datatype":_datatype, "constraint":_constraint }
# ignore None values
plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v))
$$ LANGUAGE plpythonu;
SELECT raise_exception('hello', 'world');
ERROR: plpy.Error: hello
DETAIL: world
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_exception", line 6, in <module>
plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v))
PL/Python function "raise_exception"
SELECT raise_exception('message text', 'detail text', _sqlstate => 'YY333');
ERROR: plpy.Error: message text
DETAIL: detail text
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_exception", line 6, in <module>
plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v))
PL/Python function "raise_exception"
SELECT raise_exception(_message => 'message text',
_detail => 'detail text',
_hint => 'hint text',
_sqlstate => 'XX555',
_schema => 'schema text',
_table => 'table text',
_column => 'column text',
_datatype => 'datatype text',
_constraint => 'constraint text');
ERROR: plpy.Error: message text
DETAIL: detail text
HINT: hint text
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_exception", line 6, in <module>
plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v))
PL/Python function "raise_exception"
SELECT raise_exception(_message => 'message text',
_hint => 'hint text',
_schema => 'schema text',
_column => 'column text',
_constraint => 'constraint text');
ERROR: plpy.Error: message text
HINT: hint text
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_exception", line 6, in <module>
plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v))
PL/Python function "raise_exception"
DO $$
DECLARE
__message text;
__detail text;
__hint text;
__sqlstate text;
__schema_name text;
__table_name text;
__column_name text;
__datatype text;
__constraint text;
BEGIN
BEGIN
PERFORM raise_exception(_message => 'message text',
_detail => 'detail text',
_hint => 'hint text',
_sqlstate => 'XX555',
_schema => 'schema text',
_table => 'table text',
_column => 'column text',
_datatype => 'datatype text',
_constraint => 'constraint text');
EXCEPTION WHEN SQLSTATE 'XX555' THEN
GET STACKED DIAGNOSTICS __message = MESSAGE_TEXT,
__detail = PG_EXCEPTION_DETAIL,
__hint = PG_EXCEPTION_HINT,
__sqlstate = RETURNED_SQLSTATE,
__schema_name = SCHEMA_NAME,
__table_name = TABLE_NAME,
__column_name = COLUMN_NAME,
__datatype = PG_DATATYPE_NAME,
__constraint = CONSTRAINT_NAME;
RAISE NOTICE 'handled exception'
USING DETAIL = format('message:(%s), detail:(%s), hint: (%s), sqlstate: (%s), '
'schema:(%s), table:(%s), column:(%s), datatype:(%s), constraint:(%s)',
__message, __detail, __hint, __sqlstate, __schema_name,
__table_name, __column_name, __datatype, __constraint);
END;
END;
$$;
NOTICE: handled exception
DETAIL: message:(plpy.Error: message text), detail:(detail text), hint: (hint text), sqlstate: (XX555), schema:(schema text), table:(table text), column:(column text), datatype:(datatype text), constraint:(constraint text)
-- the displayed context is different between Python2 and Python3,
-- but that's not important for this test
\set SHOW_CONTEXT never
do $$
try:
plpy.execute("select raise_exception(_message => 'my message', _sqlstate => 'XX987', _hint => 'some hint', _table=> 'users_tab', _datatype => 'user_type')")
except Exception, e:
plpy.info(e.spidata)
raise e
$$ LANGUAGE plpythonu;
INFO: (119577128, None, 'some hint', None, 0, None, 'users_tab', None, 'user_type', None)
ERROR: plpy.SPIError: plpy.Error: my message
HINT: some hint
do $$
try:
plpy.error(message = 'my message', sqlstate = 'XX987', hint = 'some hint', table = 'users_tab', datatype = 'user_type')
except Exception, e:
plpy.info('sqlstate: %s, hint: %s, tablename: %s, datatype: %s' % (e.sqlstate, e.hint, e.table_name, e.datatype_name))
raise e
$$ LANGUAGE plpythonu;
INFO: sqlstate: XX987, hint: some hint, tablename: users_tab, datatype: user_type
ERROR: plpy.Error: my message
HINT: some hint
|
