The plyp_wrappers Module

Postgres PLPY Types

The types below are used just for type hinting. They are types that only live in the postgres runtime.

plpy_wrappers.PLyPlan
plpy_wrappers.PLyResult

PLPLYWrapper

class PLPYWrapper(postgres_runtime_globals)

much documentation is taken from https://www.postgresql.org/docs/11/ wrapper around plpython plpy library which is included by default in each plpython language procedure/function

class MessageKWARGS(detail=None, hint=None, sqlstate=None, schema_name=None, table_name=None, column_name=None, datatype_name=None, constraint_name=None)

see https://www.postgresql.org/docs/11/plpython-util.html for more information. This class represents the available key word arguments to add to a message for more detailed information

__init__(detail=None, hint=None, sqlstate=None, schema_name=None, table_name=None, column_name=None, datatype_name=None, constraint_name=None)
Return type

None

Parameters

  • detail (str) –

  • hint (str) –

  • sqlstate (str) –

  • schema_name (str) –

  • table_name (str) –

  • column_name (str) –

  • datatype_name (str) –

  • constraint_name (str) –

class MessagePriority

all of the possible message priority levels

debug = 'debug'

generates a message with a priority of ‘debug’

error = 'error'

generates a message with a priority of ‘error’ and raise an exception.

Warning

This throws an exception, which, if uncaught, propagates out to the calling query, causing the current transaction or subtransaction to be aborted.

fatal = 'fatal'

generates a message with a priority of ‘fatal’ and raise an exception.

Warning

This throws an exception, which, propagates out to the calling query, causing the current transaction or subtransaction to be aborted.

info = 'info'

generates a message with a priority of ‘info’

log = 'log'

generates a message with a priority of ‘log’

notice = 'notice'

generates a message with a priority of ‘notice’

warning = 'warning'

generates a message with a priority of ‘warning’

__init__(postgres_runtime_globals)
Parameters

postgres_runtime_globals (dict) – called from within the postgres plpython runtime by using

>>> from plpy_wrapper import PLPYWrapper
>>> plpy_wrapper = PLPYWrapper(globals())
commit()

commits the current transaction

Return type

None

execute(query)
Parameters

query (str) – the SQL string to execute

Return type

ResultSet

Returns

a ResultSet

execute_plan(plan, args, row_limit=None)

see https://www.postgresql.org/docs/11/plpython-database.html for more information

Return type

ResultSet

Parameters

  • plan (PLyPlan) –

  • args (List[Any]) –

execute_with_transaction(query)

see https://www.postgresql.org/docs/11/plpython-transactions.html executes a the given query in a transaction and commits. If an exception is encountered, the transaction is rolled back :type query: str :param query: the SQL to run :rtype: ResultSet :return: the ResultSets :raises `plpy.SPIError

Parameters

query (str) –

Return type

plpy_wrapper.plpy_wrappers.ResultSet

prepare(query, argtypes=None)

prepares a query plan

Parameters

  • query (str) – the SQL string

  • argtypes (Optional[List[str]]) – types of args that will be interpolated into the query upon calling execute_plan()

Return type

~PLyPlan

publish_message(message_priority, message, message_kwargs=None)

magic function that calls the appropriate plpy function based on the message priority given instead of writing

>>> from plpy_wrapper import PLPYWrapper
>>> wrapper = PLPYWrapper(globals())
>>> wrapper.plpy.notice("message here",detail='details here')

we can do something like this. The advantage is mainly in enforcing the kwargs for all of the message types But also enables for dynamic message priority setting

>>> from plpy_wrapper import PLPYWrapper
>>> wrapper = PLPYWrapper(globals())
>>> wrapper.publish_message(PLPYWrapper.MessagePriority.notice,"message here",PLPYWrapper.MessageKWARGS(detail="details here"))
Parameters

  • message_priority (MessagePriority) – this determines which plpy message function is called

  • message (str) – the actual message text

  • message_kwargs (Optional[MessageKWARGS]) – optional keyword arguments to provide to the message

Return type

None

rollback()

rolls back the current transaction

Return type

None

subtransaction()

ghost wrapper around plpy subtransaction. Example usage:

>>> from plpy_wrapper import PLPYWrapper
>>> wrapper = PLPYWrapper(globals())
>>> with wrapper.subtransaction():
>>>  pass
>>>  #do subtransaction stuff here
Return type

None

ResultSet

class ResultSet(result_set)

wrapper around result of query in plpy https://www.postgresql.org/docs/11/plpython-database.html

__init__(result_set)
Parameters

result_set (~PLyResult) – the type expected is the output of plpy.execute, plpy being postgres’s native python package

property colnames

returns a list of column names

Return type

List[str]

property coltypes

returns list of column type OID s

Return type

List[int]

property coltypmods

returns a list of type-specific type modifiers for the columns

Return type

List[Union[str, int]]

property n_rows

Returns the number of rows processed by the command

Return type

int

property status

The SPI_execute() return value

Return type

int

Row

class Row(row_dict)

wrapper around an individual result from the result set or TD['new'] / TD['old'] ResultSet contains Row objects are returned

__init__(row_dict)
Parameters

row_dict (dict) – dict containing keys as column names and corresponding values as column values

property row_dict

Get the row dictionary at its currents state. Modifying this dictionary directly won’t do anything. Instead of modifying directly, modify the attribute on the object itself, like so

>>> from plpy_wrapper import PLPYWrapper
>>> plpy_wrapper = PLPYWrapper(globals())
>>> row = plpy_wrapper.execute('select id,name from customer.contact LIMIT 1')[0]
>>> row.name = 'new name'