codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!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.3 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>3 This section contains a very simple example of SPI usage. The4 C function <code class="function">execq</code> takes an SQL command as its5 first argument and a row count as its second, executes the command6 using <code class="function">SPI_exec</code> and returns the number of rows7 that were processed by the command. You can find more complex8 examples for SPI in the source tree in9 <code class="filename">src/test/regress/regress.c</code> and in the10 <a class="xref" href="contrib-spi.html" title="F.41. spi — Server Programming Interface features/examples">spi</a> module.11 </p><pre class="programlisting">12#include "postgres.h"13 14#include "executor/spi.h"15#include "utils/builtins.h"16 17PG_MODULE_MAGIC;18 19PG_FUNCTION_INFO_V1(execq);20 21Datum22execq(PG_FUNCTION_ARGS)23{24 char *command;25 int cnt;26 int ret;27 uint64 proc;28 29 /* Convert given text object to a C string */30 command = text_to_cstring(PG_GETARG_TEXT_PP(0));31 cnt = PG_GETARG_INT32(1);32 33 SPI_connect();34 35 ret = SPI_exec(command, cnt);36 37 proc = SPI_processed;38 39 /*40 * If some rows were fetched, print them via elog(INFO).41 */42 if (ret > 0 && SPI_tuptable != NULL)43 {44 SPITupleTable *tuptable = SPI_tuptable;45 TupleDesc tupdesc = tuptable->tupdesc;46 char buf[8192];47 uint64 j;48 49 for (j = 0; j < tuptable->numvals; j++)50 {51 HeapTuple tuple = tuptable->vals[j];52 int i;53 54 for (i = 1, buf[0] = 0; i <= tupdesc->natts; i++)55 snprintf(buf + strlen(buf), sizeof(buf) - strlen(buf), " %s%s",56 SPI_getvalue(tuple, tupdesc, i),57 (i == tupdesc->natts) ? " " : " |");58 elog(INFO, "EXECQ: %s", buf);59 }60 }61 62 SPI_finish();63 pfree(command);64 65 PG_RETURN_INT64(proc);66}67</pre><p>68 This is how you declare the function after having compiled it into69 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>.):70 71</p><pre class="programlisting">72CREATE FUNCTION execq(text, integer) RETURNS int873 AS '<em class="replaceable"><code>filename</code></em>'74 LANGUAGE C STRICT;75</pre><p>76 </p><p>77 Here is a sample session:78 79</p><pre class="programlisting">80=> SELECT execq('CREATE TABLE a (x integer)', 0);81 execq82-------83 084(1 row)85 86=> INSERT INTO a VALUES (execq('INSERT INTO a VALUES (0)', 0));87INSERT 0 188=> SELECT execq('SELECT * FROM a', 0);89INFO: EXECQ: 0 <em class="lineannotation"><span class="lineannotation">-- inserted by execq</span></em>90INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- returned by execq and inserted by upper INSERT</span></em>91 92 execq93-------94 295(1 row)96 97=> SELECT execq('INSERT INTO a SELECT x + 2 FROM a RETURNING *', 1);98INFO: EXECQ: 2 <em class="lineannotation"><span class="lineannotation">-- 0 + 2, then execution was stopped by count</span></em>99 execq100-------101 1102(1 row)103 104=> SELECT execq('SELECT * FROM a', 10);105INFO: EXECQ: 0106INFO: EXECQ: 1107INFO: EXECQ: 2108 109 execq110-------111 3 <em class="lineannotation"><span class="lineannotation">-- 10 is the max value only, 3 is the real number of rows</span></em>112(1 row)113 114=> SELECT execq('INSERT INTO a SELECT x + 10 FROM a', 1);115 execq116-------117 3 <em class="lineannotation"><span class="lineannotation">-- all rows processed; count does not stop it, because nothing is returned</span></em>118(1 row)119 120=> SELECT * FROM a;121 x122----123 0124 1125 2126 10127 11128 12129(6 rows)130 131=> DELETE FROM a;132DELETE 6133=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);134INSERT 0 1135=> SELECT * FROM a;136 x137---138 1 <em class="lineannotation"><span class="lineannotation">-- 0 (no rows in a) + 1</span></em>139(1 row)140 141=> INSERT INTO a VALUES (execq('SELECT * FROM a', 0) + 1);142INFO: EXECQ: 1143INSERT 0 1144=> SELECT * FROM a;145 x146---147 1148 2 <em class="lineannotation"><span class="lineannotation">-- 1 (there was one row in a) + 1</span></em>149(2 rows)150 151<em class="lineannotation"><span class="lineannotation">-- This demonstrates the data changes visibility rule.</span></em>152<em class="lineannotation"><span class="lineannotation">-- execq is called twice and sees different numbers of rows each time:</span></em>153 154=> INSERT INTO a SELECT execq('SELECT * FROM a', 0) * x FROM a;155INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- results from first execq</span></em>156INFO: EXECQ: 2157INFO: EXECQ: 1 <em class="lineannotation"><span class="lineannotation">-- results from second execq</span></em>158INFO: EXECQ: 2159INFO: EXECQ: 2160INSERT 0 2161=> SELECT * FROM a;162 x163---164 1165 2166 2 <em class="lineannotation"><span class="lineannotation">-- 2 rows * 1 (x in first row)</span></em>167 6 <em class="lineannotation"><span class="lineannotation">-- 3 rows (2 + 1 just inserted) * 2 (x in second row)</span></em>168(4 rows)169</pre><p>170 </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.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> Chapter 48. Background Worker Processes</td></tr></table></div></body></html>