| 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='XX001', # GPDB: Don't use Use XX000 (ERRCODE_INTERNAL_ERROR), because it causes the source file location to be printed |
| 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 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: 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 plpythonu; |
| 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 plpythonu; |
| 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 plpythonu; |
| 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 plgsql |
| 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 plpythonu; |
| 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) |
| -- 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_name => 'users_tab', _datatype_name => 'user_type')") |
| except Exception as 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_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 plpythonu; |
| INFO: sqlstate: XX987, hint: some hint, table_name: users_tab, datatype_name: user_type |
| ERROR: plpy.Error: my message |
| HINT: some hint |
