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 (SPI). SPI gives programmer the ability to run SQL queries via a set of interface functions for using PostgreSQL's parser, planner, and executor.
Note
If the SQL statement 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 an involved process not yet officially declared "stable" and therefore, it is not present in the documentation on C extensions. If you need it, one good place to look at would be the source code for various pluggable languages (PL/python, PL/proxy, and so on) which do it and are likely to be maintained in good order if the interface changes.
In the PL/python...