|
- <?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.4 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 > 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="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">
- => 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">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>
|
