<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>47.6. Examples</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="spi-visibility.html" title="47.5. Visibility of Data Changes" /><link rel="next" href="bgworker.html" title="Chapter 48. Background Worker Processes" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">47.6. Examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="spi-visibility.html" title="47.5. Visibility of Data Changes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="spi.html" title="Chapter 47. Server Programming Interface">Up</a></td><th width="60%" align="center">Chapter 47. Server Programming Interface</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="bgworker.html" title="Chapter 48. Background Worker Processes">Next</a></td></tr></table><hr></hr></div><div class="sect1" id="SPI-EXAMPLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">47.6. Examples</h2></div></div></div><p>
This section contains a very simple example of SPI usage. The
C function <code class="function">execq</code> takes an SQL command as its
first argument and a row count as its second, executes the command
using <code class="function">SPI_exec</code> and returns the number of rows
that were processed by the command. You can find more complex
examples for SPI in the source tree in
<code class="filename">src/test/regress/regress.c</code> and in the
<a class="xref" href="contrib-spi.html" title="F.36. spi">spi</a> module.
</p><pre class="programlisting">
#include "postgres.h"
#include "executor/spi.h"
#include "utils/builtins.h"
PG_MODULE_MAGIC;
PG_FUNCTION_INFO_V1(execq);
Datum
execq(PG_FUNCTION_ARGS)
{
char *command;
int cnt;
int ret;
uint64 proc;
/* Convert given text object to a C string */
command = text_to_cstring(PG_GETARG_TEXT_PP(0));
cnt = PG_GETARG_INT32(1);
SPI_connect();
ret = SPI_exec(command, cnt);
proc = SPI_processed;
/*
* If some rows were fetched, print them via elog(INFO).
*/
if (ret > 0 && SPI_tuptable != NULL)
{
TupleDesc tupdesc = SPI_tuptable->tupdesc;
SPITupleTable *tuptable = SPI_tuptable;
char buf[8192];
uint64 j;
for (j = 0; j < proc; j++)
{
HeapTuple tuple = tuptable->vals[j];
int i;
for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)
snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s",
SPI_getvalue(tuple, tupdesc, i),
(i == tupdesc->natts) ? " " : " |");
elog(INFO, "EXECQ: %s", buf);
}
}
SPI_finish();
pfree(command);
PG_RETURN_INT64(proc);
}
</pre><p>
This is how you declare the function after having compiled it into
a shared library (details are in <a class="xref" href="xfunc-c.html#DFUNC" title="38.10.5. Compiling and Linking Dynamically-loaded Functions">Section 38.10.5</a>.):
</p><pre class="programlisting">
CREATE FUNCTION execq(text, integer) RETURNS int8
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C STRICT;
</pre><p>
</p><p>
Here is a sample session:
</p><pre class="programlisting">
=> SELECT execq('CREATE TABLE a (x integer)', 0);
execq
-------
0
(1 row)
=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
INSERT 0 1
=> SELECT execq('SELECT * FROM a', 0);
INFO: EXECQ: 0 -- inserted by execq
INFO: EXECQ: 1 -- returned by execq and inserted by upper INSERT
execq
-------
2
(1 row)
=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
execq
-------
1
(1 row)
=> SELECT execq('SELECT * FROM a', 10);
INFO: EXECQ: 0
INFO: EXECQ: 1
INFO: EXECQ: 2 -- 0 + 2, only one row inserted - as specified
execq
-------
3 -- 10 is the max value only, 3 is the real number of rows
(1 row)
=> DELETE FROM a;
DELETE 3
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INSERT 0 1
=> SELECT * FROM a;
x
---
1 -- no rows in a (0) + 1
(1 row)
=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
INFO: EXECQ: 1
INSERT 0 1
=> SELECT * FROM a;
x
---
1
2 -- there was one row in a + 1
(2 rows)
-- This demonstrates the data changes visibility rule:
=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;
INFO: EXECQ: 1
INFO: EXECQ: 2
INFO: EXECQ: 1
INFO: EXECQ: 2
INFO: EXECQ: 2
INSERT 0 2
=> SELECT * FROM a;
x
---
1
2
2 -- 2 rows * 1 (x in first row)
6 -- 3 rows (2 + 1 just inserted) * 2 (x in second row)
(4 rows) ^^^^^^
rows visible to execq() in different invocations
</pre><p>
</p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="spi-visibility.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="spi.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="bgworker.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">47.5. Visibility of Data Changes </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 48. Background Worker Processes</td></tr></table></div></body></html>
