<title>Utility Functions</title>

<para>

The <literal>plpy</literal> module also provides the functions

<literal>plpy.debug(<replaceable>msg</>)</literal>,

<literal>plpy.log(<replaceable>msg</>)</literal>,

<literal>plpy.info(<replaceable>msg</>)</literal>,

<literal>plpy.notice(<replaceable>msg</>)</literal>,

<literal>plpy.warning(<replaceable>msg</>)</literal>,

<literal>plpy.error(<replaceable>msg</>)</literal>, and

<literal>plpy.fatal(<replaceable>msg</>)</literal>.<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>

<function>plpy.error</function> and

<function>plpy.fatal</function>actually raise a Python exception

which, if uncaught, propagates out to the calling query, causing

the current transaction or subtransaction to be aborted.

<literal>raise plpy.Error(<replaceable>msg</>)</literal> and

<literal>plpy.debug(<replaceable>msg, **kwargs</>)</literal>,

<literal>plpy.log(<replaceable>msg, **kwargs</>)</literal>,

<literal>plpy.info(<replaceable>msg, **kwargs</>)</literal>,

<literal>plpy.notice(<replaceable>msg, **kwargs</>)</literal>,

<literal>plpy.warning(<replaceable>msg, **kwargs</>)</literal>,

<literal>plpy.error(<replaceable>msg, **kwargs</>)</literal>, and

<literal>plpy.fatal(<replaceable>msg, **kwargs</>)</literal>.

<indexterm><primary>elog</><secondary>in PL/Python</></indexterm>

<function>plpy.error</function>and <function>plpy.fatal</function>

actually raise a Python exceptionwhich, if uncaught, propagates out to

thecalling query, causing thecurrent transaction or subtransaction to

be aborted.<literal>raise plpy.Error(<replaceable>msg</>)</literal> and

<literal>raise plpy.Fatal(<replaceable>msg</>)</literal> are

equivalent to calling

<function>plpy.error</function> and

<function>plpy.fatal</function>, respectively.

The other functions only generate messages of different

priority levels.

equivalent to calling <literal>plpy.error(<replaceable>msg</>)</literal> and

<literal>plpy.fatal(<replaceable>msg</>)</literal>, respectively but

the <literal>raise</literal> form does not allow passing keyword arguments.

The other functions only generate messages of different priority levels.

Whether messages of a particular priority are reported to the client,

written to the server log, or both is controlled by the

<xref linkend="guc-log-min-messages"> and

<xref linkend="guc-client-min-messages"> configuration

variables. See <xref linkend="runtime-config"> for more information.

</para>

<para>

The <replaceable>msg</> argument is given as a positional argument. For

backward compatibility, more than one positional argument can be given. In

that case, the string representation of the tuple of positional arguments

becomes the message reported to the client.

The following keyword-only arguments are accepted:

<literal>

<replaceable>detail</replaceable>, <replaceable>hint</replaceable>,

<replaceable>sqlstate</replaceable>, <replaceable>schema</replaceable>,

<replaceable>table</replaceable>, <replaceable>column</replaceable>,

<replaceable>datatype</replaceable> , <replaceable>constraint</replaceable>

</literal>.

The string representation of the objects passed as keyword-only arguments

is used to enrich the messages reported to the client. For example:

<programlisting>

CREATE FUNCTION raise_custom_exception() RETURNS void AS $$

plpy.error("custom exception message", detail = "some info about exception", hint = "hint for users")

$$ LANGUAGE plpythonu;

postgres=# select raise_custom_exception();

ERROR: XX000: plpy.Error: custom exception message

DETAIL: some info about exception

HINT: hint for users

CONTEXT: Traceback (most recent call last):

PL/Python function "raise_custom_exception", line 2, in &lt;module&gt;

plpy.error("custom exception message", detail = "some info about exception", hint = "hint for users")

PL/Python function "raise_custom_exception"

LOCATION: PLy_elog, plpy_elog.c:132

</programlisting>

</para>

<para>

Another set of utility functions are

<literal>plpy.quote_literal(<replaceable>string</>)</literal>,

Error, Fatal, SPIError, cursor, debug, error, execute, fatal, info, log, notice, prepare, quote_ident, quote_literal, quote_nullable, spiexceptions, subtransaction, warning

(1 row)

CREATE FUNCTIONelog_test() RETURNS void

CREATE FUNCTIONelog_test_basic() RETURNS void

AS $$

plpy.debug('debug')

plpy.log('log')

plpy.warning('warning')

plpy.error('error')

$$ LANGUAGE plpythonu;

SELECTelog_test();

SELECTelog_test_basic();

INFO: info

INFO: 37

INFO: ()

WARNING: warning

ERROR: plpy.Error: error

CONTEXT: Traceback (most recent call last):

PL/Python function "elog_test", line 10, in <module>

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