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...