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
|
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_name='any info about schema',
table_name='any info about table',
column_name='any info about column',
datatype_name='any info about datatype',
constraint_name='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 plpython3u;
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 plpython3u;
INFO: other types
DETAIL: (10, 20)
DO $$
import time;
from datetime import date
plpy.info('other types', detail=date(2016, 2, 26))
$$ LANGUAGE plpython3u;
INFO: other types
DETAIL: 2016-02-26
DO $$
basket = ['apple', 'orange', 'apple', 'pear', 'orange', 'banana']
plpy.info('other types', detail=basket)
$$ LANGUAGE plpython3u;
INFO: other types
DETAIL: ['apple', 'orange', 'apple', 'pear', 'orange', 'banana']
-- should fail
DO $$ plpy.info('wrong sqlstate', sqlstate='54444A') $$ LANGUAGE plpython3u;
ERROR: ValueError: invalid SQLSTATE code
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 1, in <module>
plpy.info('wrong sqlstate', sqlstate='54444A')
PL/Python anonymous code block
DO $$ plpy.info('unsupported argument', blabla='fooboo') $$ LANGUAGE plpython3u;
ERROR: TypeError: 'blabla' is an invalid keyword argument for this function
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 1, in <module>
plpy.info('unsupported argument', blabla='fooboo')
PL/Python anonymous code block
DO $$ plpy.info('first message', message='second message') $$ LANGUAGE plpython3u;
ERROR: TypeError: argument 'message' given by name and position
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 1, in <module>
plpy.info('first message', message='second message')
PL/Python anonymous code block
DO $$ plpy.info('first message', 'second message', message='third message') $$ LANGUAGE plpython3u;
ERROR: TypeError: argument 'message' given by name and position
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 1, in <module>
plpy.info('first message', 'second message', message='third message')
PL/Python anonymous code block
-- raise exception in python, handle exception in plpgsql
CREATE OR REPLACE FUNCTION raise_exception(_message text, _detail text DEFAULT NULL, _hint text DEFAULT NULL,
_sqlstate text DEFAULT NULL,
_schema_name text DEFAULT NULL,
_table_name text DEFAULT NULL,
_column_name text DEFAULT NULL,
_datatype_name text DEFAULT NULL,
_constraint_name text DEFAULT NULL)
RETURNS void AS $$
kwargs = {
"message": _message, "detail": _detail, "hint": _hint,
"sqlstate": _sqlstate, "schema_name": _schema_name, "table_name": _table_name,
"column_name": _column_name, "datatype_name": _datatype_name,
"constraint_name": _constraint_name
}
# ignore None values
plpy.error(**dict((k, v) for k, v in iter(kwargs.items()) if v))
$$ LANGUAGE plpython3u;
SELECT raise_exception('hello', 'world');
ERROR: plpy.Error: hello
DETAIL: world
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_exception", line 9, 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 9, 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_name => 'schema text',
_table_name => 'table text',
_column_name => 'column text',
_datatype_name => 'datatype text',
_constraint_name => '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 9, 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_name => 'schema text',
_column_name => 'column text',
_constraint_name => 'constraint text');
ERROR: plpy.Error: message text
HINT: hint text
CONTEXT: Traceback (most recent call last):
PL/Python function "raise_exception", line 9, 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_name text;
__constraint_name text;
BEGIN
BEGIN
PERFORM raise_exception(_message => 'message text',
_detail => 'detail text',
_hint => 'hint text',
_sqlstate => 'XX555',
_schema_name => 'schema text',
_table_name => 'table text',
_column_name => 'column text',
_datatype_name => 'datatype text',
_constraint_name => '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_name = PG_DATATYPE_NAME,
__constraint_name = CONSTRAINT_NAME;
RAISE NOTICE 'handled exception'
USING DETAIL = format('message:(%s), detail:(%s), hint: (%s), sqlstate: (%s), '
'schema_name:(%s), table_name:(%s), column_name:(%s), datatype_name:(%s), constraint_name:(%s)',
__message, __detail, __hint, __sqlstate, __schema_name,
__table_name, __column_name, __datatype_name, __constraint_name);
END;
END;
$$;
NOTICE: handled exception
DETAIL: message:(plpy.Error: message text), detail:(detail text), hint: (hint text), sqlstate: (XX555), schema_name:(schema text), table_name:(table text), column_name:(column text), datatype_name:(datatype text), constraint_name:(constraint text)
DO $$
try:
plpy.execute("select raise_exception(_message => 'my message', _sqlstate => 'XX987', _hint => 'some hint', _table_name => 'users_tab', _datatype_name => 'user_type')")
except Exception as e:
plpy.info(e.spidata)
raise e
$$ LANGUAGE plpython3u;
INFO: (119577128, None, 'some hint', None, 0, None, 'users_tab', None, 'user_type', None)
ERROR: plpy.SPIError: plpy.Error: my message
HINT: some hint
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 6, in <module>
raise e
PL/Python anonymous code block, line 3, in __plpython_inline_block
plpy.execute("select raise_exception(_message => 'my message', _sqlstate => 'XX987', _hint => 'some hint', _table_name => 'users_tab', _datatype_name => 'user_type')")
PL/Python anonymous code block
DO $$
try:
plpy.error(message = 'my message', sqlstate = 'XX987', hint = 'some hint', table_name = 'users_tab', datatype_name = 'user_type')
except Exception as e:
plpy.info('sqlstate: %s, hint: %s, table_name: %s, datatype_name: %s' % (e.sqlstate, e.hint, e.table_name, e.datatype_name))
raise e
$$ LANGUAGE plpython3u;
INFO: sqlstate: XX987, hint: some hint, table_name: users_tab, datatype_name: user_type
ERROR: plpy.Error: my message
HINT: some hint
CONTEXT: Traceback (most recent call last):
PL/Python anonymous code block, line 6, in <module>
raise e
PL/Python anonymous code block, line 3, in __plpython_inline_block
plpy.error(message = 'my message', sqlstate = 'XX987', hint = 'some hint', table_name = 'users_tab', datatype_name = 'user_type')
PL/Python anonymous code block
|
