Database Reference
In-Depth Information
Running
queries
and
calling
PostgreSQL functions
Our next stop is running SQL queries inside the database. When you want to run a
query against the database, you need to use something called Server Programming
Interface (or SPI for short). SPI gives programmer the ability to run SQL queries via a
set of interface functions for using PostgreSQLs parser, planner, and executor.
Note
If the SQL you are running via SPI fails, the control is not returned to the caller,
but instead the system reverts to a clean state via internal mechanisms for
ROLLBACK
. It is possible to catch SQL errors by establishing a
sub-transaction
around your calls. It is a bit involved process not yet officially declared "stable" and
thus Therefore, it not present in the documentation on C extensions. If you need it,
one good place to look at would be source code for various pluggable languages
(
pl/python
,
pl/proxy
, …) which do it and are likely to be maintained in good
order if the interface changes.
In PL/Python source, the functions to examine are in the
plpython/
plpy_spi.c
file
and
are
appropriately
named
Ply_spi_subtransaction_[begin|commit|abort]()
.
The SPI functions do return non-negative values for success, either directly via return
value or in global variable
SPI_result
. Errors produce a negative value or
Null
.
Sample C function using SPI
Here is a sample function doing an SQL query via
SPI_*()
functions. It is a modified
version of the sample form standard documentation (it uses
Version 1 Calling Con-
ventions
and outputs a few more bit of information). The
.c
,
.sql.in
, and
Make-
file
functions for this sample are available in the
spi_samples/
subdirectory.
