Database Reference
In-Depth Information
To show what was returned by the query and also to show how to access fields re-
turned by SPI functions, we next print out detailed info any tuples returned by the
query via
ereport(INFO, …)
call. We first checked that the
SPI_exec
call was
successful (
ret > 0
) and that some tuples were returned (
SPI_tuptable !=
NULL
). and then for each returned tuple
for(j = 0; j < proc; ...)
we looped
over the fields
for(i = 1; i <= tupdesc->natts;...)
formatting the fields
info into a buffer. We get the string representations of field name, value, and data
type using SPI functions
SPI_fname()
,
SPI_getvalue()
, and
SPI_gettype()
and then send the row to user using
ereport(INFO, …)
. If you want to return the
values from the function instead, see next sections on returning
SETOF
values and
composite types.
Finally, we freed the SPI internal state using
SPI_finish();
. One can also free
the space allocated for the command variable by
text_to_cstring(<textarg>)
function, though it is not strictly necessary thanks to the function call context being
destroyed and memory allocated in it being freed anyway at the function exit.
Visibility of data changes
The visibility rules for data changes in PostgreSQL are that each command cannot
see it's own changes but usually can see changes made by commands which were
started before it, even when the command is started by the outer command or query.
The exception is when the query is executed with read-only flag set, in which case
the changes made by outer commands are invisible to inner or called commands.
The visibility rules are described in the documentation at
http://www.postgresql.org/
docs/current/static/spi-visibility.html
and may be quite complex to understand at first,
but it may help to think of a read-only
SPI_execute()
call as being command-
level, similar to transaction isolation level Serializable and read-write call as similar
to Read-Committed isolation level.
Note
The
read-write
flag of
SPI_execute()
does not enforce read-only transac-
tion state!
