diff options
Diffstat (limited to 'doc/src/sgml/html/spi-examples.html')
| -rw-r--r-- | doc/src/sgml/html/spi-examples.html | 170 |
1 files changed, 170 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..5bdf163 --- /dev/null +++ b/doc/src/sgml/html/spi-examples.html @@ -0,0 +1,170 @@ +<?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 id="docContent" class="container-fluid col-10"><div 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 16.2 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 /></div><div class="sect1" id="SPI-EXAMPLES"><div class="titlepage"><div><div><h2 class="title" style="clear: both">47.6. Examples <a href="#SPI-EXAMPLES" class="id_link">#</a></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.41. spi — Server Programming Interface features/examples">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) + { + SPITupleTable *tuptable = SPI_tuptable; + TupleDesc tupdesc = tuptable->tupdesc; + char buf[8192]; + uint64 j; + + for (j = 0; j < tuptable->numvals; 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 <em class="lineannotation"><span class="lineannotation">-- inserted by execq</span></em> +INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- returned by execq and inserted by upper INSERT</span></em> + + execq +------- + 2 +(1 row) + +=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a RETURNING *', 1); +INFO: EXECQ: 2 <em class="lineannotation"><span class="lineannotation">-- 0 + 2, then execution was stopped by count</span></em> + execq +------- + 1 +(1 row) + +=> SELECT execq('SELECT * FROM a', 10); +INFO: EXECQ: 0 +INFO: EXECQ: 1 +INFO: EXECQ: 2 + + execq +------- + 3 <em class="lineannotation"><span class="lineannotation">-- 10 is the max value only, 3 is the real number of rows</span></em> +(1 row) + +=> SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1); + execq +------- + 3 <em class="lineannotation"><span class="lineannotation">-- all rows processed; count does not stop it, because nothing is returned</span></em> +(1 row) + +=> SELECT * FROM a; + x +---- + 0 + 1 + 2 + 10 + 11 + 12 +(6 rows) + +=> DELETE FROM a; +DELETE 6 +=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); +INSERT 0 1 +=> SELECT * FROM a; + x +--- + 1 <em class="lineannotation"><span class="lineannotation">-- 0 (no rows in a) + 1</span></em> +(1 row) + +=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1); +INFO: EXECQ: 1 +INSERT 0 1 +=> SELECT * FROM a; + x +--- + 1 + 2 <em class="lineannotation"><span class="lineannotation">-- 1 (there was one row in a) + 1</span></em> +(2 rows) + +<em class="lineannotation"><span class="lineannotation">-- This demonstrates the data changes visibility rule.</span></em> +<em class="lineannotation"><span class="lineannotation">-- execq is called twice and sees different numbers of rows each time:</span></em> + +=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a; +INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- results from first execq</span></em> +INFO: EXECQ: 2 +INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- results from second execq</span></em> +INFO: EXECQ: 2 +INFO: EXECQ: 2 +INSERT 0 2 +=> SELECT * FROM a; + x +--- + 1 + 2 + 2 <em class="lineannotation"><span class="lineannotation">-- 2 rows * 1 (x in first row)</span></em> + 6 <em class="lineannotation"><span class="lineannotation">-- 3 rows (2 + 1 just inserted) * 2 (x in second row)</span></em> +(4 rows) +</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" title="47.5. Visibility of Data Changes">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="spi.html" title="Chapter 47. Server Programming Interface">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="bgworker.html" title="Chapter 48. Background Worker Processes">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" title="PostgreSQL 16.2 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 48. Background Worker Processes</td></tr></table></div></body></html>
\ No newline at end of file |