summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/spi-examples.html
diff options
context:
space:
mode:
Diffstat (limited to 'doc/src/sgml/html/spi-examples.html')
-rw-r--r--doc/src/sgml/html/spi-examples.html152
1 files changed, 152 insertions, 0 deletions
diff --git a/doc/src/sgml/html/spi-examples.html b/doc/src/sgml/html/spi-examples.html
new file mode 100644
index 0000000..0d4f277
--- /dev/null
+++ b/doc/src/sgml/html/spi-examples.html
@@ -0,0 +1,152 @@
+<?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>46.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 V1.79.1" /><link rel="prev" href="spi-visibility.html" title="46.5. Visibility of Data Changes" /><link rel="next" href="bgworker.html" title="Chapter 47. 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">46.6. Examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="spi-visibility.html" title="46.5. Visibility of Data Changes">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="spi.html" title="Chapter 46. Server Programming Interface">Up</a></td><th width="60%" align="center">Chapter 46. Server Programming Interface</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 12.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="bgworker.html" title="Chapter 47. 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">46.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 &gt; 0 &amp;&amp; SPI_tuptable != NULL)
+ {
+ TupleDesc tupdesc = SPI_tuptable-&gt;tupdesc;
+ SPITupleTable *tuptable = SPI_tuptable;
+ char buf[8192];
+ uint64 j;
+
+ for (j = 0; j &lt; proc; j++)
+ {
+ HeapTuple tuple = tuptable-&gt;vals[j];
+ int i;
+
+ for (i = 1, buf[0] = 0; i &lt;= tupdesc-&gt;natts; i++)
+ snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s",
+ SPI_getvalue(tuple, tupdesc, i),
+ (i == tupdesc-&gt;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="37.10.5. Compiling and Linking Dynamically-Loaded Functions">Section 37.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">
+=&gt; SELECT execq('CREATE TABLE a (x integer)', 0);
+ execq
+-------
+ 0
+(1 row)
+
+=&gt; INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));
+INSERT 0 1
+=&gt; 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)
+
+=&gt; SELECT execq('INSERT INTO a SELECT x + 2 FROM a', 1);
+ execq
+-------
+ 1
+(1 row)
+
+=&gt; 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)
+
+=&gt; DELETE FROM a;
+DELETE 3
+=&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
+INSERT 0 1
+=&gt; SELECT * FROM a;
+ x
+---
+ 1 -- no rows in a (0) + 1
+(1 row)
+
+=&gt; INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);
+INFO: EXECQ: 1
+INSERT 0 1
+=&gt; SELECT * FROM a;
+ x
+---
+ 1
+ 2 -- there was one row in a + 1
+(2 rows)
+
+-- This demonstrates the data changes visibility rule:
+
+=&gt; 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
+=&gt; 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">46.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 47. Background Worker Processes</td></tr></table></div></body></html> \ No newline at end of file