codekingpro/portable-devtools
114k
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>F.41. spi — Server Programming Interface features/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="sepgsql.html" title="F.40. sepgsql — SELinux-, label-based mandatory access control (MAC) security module" /><link rel="next" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">F.41. spi — Server Programming Interface features/examples</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sepgsql.html" title="F.40. sepgsql — SELinux-, label-based mandatory access control (MAC) security module">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><th width="60%" align="center">Appendix F. Additional Supplied Modules and Extensions</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="sslinfo.html" title="F.42. sslinfo — obtain client SSL information">Next</a></td></tr></table><hr /></div><div class="sect1" id="CONTRIB-SPI"><div class="titlepage"><div><div><h2 class="title" style="clear: both">F.41. spi — Server Programming Interface features/examples <a href="#CONTRIB-SPI" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="contrib-spi.html#CONTRIB-SPI-REFINT">F.41.1. refint — Functions for Implementing Referential Integrity</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#CONTRIB-SPI-AUTOINC">F.41.2. autoinc — Functions for Autoincrementing Fields</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#CONTRIB-SPI-INSERT-USERNAME">F.41.3. insert_username — Functions for Tracking Who Changed a Table</a></span></dt><dt><span class="sect2"><a href="contrib-spi.html#CONTRIB-SPI-MODDATETIME">F.41.4. moddatetime — Functions for Tracking Last Modification Time</a></span></dt></dl></div><a id="id-1.11.7.51.2" class="indexterm"></a><p>3 The <span class="application">spi</span> module provides several workable examples4 of using the <a class="link" href="spi.html" title="Chapter 47. Server Programming Interface">Server Programming Interface</a>5 (<acronym class="acronym">SPI</acronym>) and triggers. While these functions are of6 some value in7 their own right, they are even more useful as examples to modify for8 your own purposes. The functions are general enough to be used9 with any table, but you have to specify table and field names (as described10 below) while creating a trigger.11 </p><p>12 Each of the groups of functions described below is provided as a13 separately-installable extension.14 </p><div class="sect2" id="CONTRIB-SPI-REFINT"><div class="titlepage"><div><div><h3 class="title">F.41.1. refint — Functions for Implementing Referential Integrity <a href="#CONTRIB-SPI-REFINT" class="id_link">#</a></h3></div></div></div><p>15 <code class="function">check_primary_key()</code> and16 <code class="function">check_foreign_key()</code> are used to check foreign key constraints.17 (This functionality is long since superseded by the built-in foreign18 key mechanism, of course, but the module is still useful as an example.)19 </p><p>20 <code class="function">check_primary_key()</code> checks the referencing table.21 To use, create a <code class="literal">BEFORE INSERT OR UPDATE</code> trigger using this22 function on a table referencing another table. Specify as the trigger23 arguments: the referencing table's column name(s) which form the foreign24 key, the referenced table name, and the column names in the referenced table25 which form the primary/unique key. To handle multiple foreign26 keys, create a trigger for each reference.27 </p><p>28 <code class="function">check_foreign_key()</code> checks the referenced table.29 To use, create a <code class="literal">BEFORE DELETE OR UPDATE</code> trigger using this30 function on a table referenced by other table(s). Specify as the trigger31 arguments: the number of referencing tables for which the function has to32 perform checking, the action if a referencing key is found33 (<code class="literal">cascade</code> — to delete the referencing row,34 <code class="literal">restrict</code> — to abort transaction if referencing keys35 exist, <code class="literal">setnull</code> — to set referencing key fields to null),36 the triggered table's column names which form the primary/unique key, then37 the referencing table name and column names (repeated for as many38 referencing tables as were specified by first argument). Note that the39 primary/unique key columns should be marked NOT NULL and should have a40 unique index.41 </p><p>42 There are examples in <code class="filename">refint.example</code>.43 </p></div><div class="sect2" id="CONTRIB-SPI-AUTOINC"><div class="titlepage"><div><div><h3 class="title">F.41.2. autoinc — Functions for Autoincrementing Fields <a href="#CONTRIB-SPI-AUTOINC" class="id_link">#</a></h3></div></div></div><p>44 <code class="function">autoinc()</code> is a trigger that stores the next value of45 a sequence into an integer field. This has some overlap with the46 built-in <span class="quote">“<span class="quote">serial column</span>”</span> feature, but it is not the same:47 <code class="function">autoinc()</code> will override attempts to substitute a48 different field value during inserts, and optionally it can be49 used to increment the field during updates, too.50 </p><p>51 To use, create a <code class="literal">BEFORE INSERT</code> (or optionally <code class="literal">BEFORE52 INSERT OR UPDATE</code>) trigger using this function. Specify two53 trigger arguments: the name of the integer column to be modified,54 and the name of the sequence object that will supply values.55 (Actually, you can specify any number of pairs of such names, if56 you'd like to update more than one autoincrementing column.)57 </p><p>58 There is an example in <code class="filename">autoinc.example</code>.59 </p></div><div class="sect2" id="CONTRIB-SPI-INSERT-USERNAME"><div class="titlepage"><div><div><h3 class="title">F.41.3. insert_username — Functions for Tracking Who Changed a Table <a href="#CONTRIB-SPI-INSERT-USERNAME" class="id_link">#</a></h3></div></div></div><p>60 <code class="function">insert_username()</code> is a trigger that stores the current61 user's name into a text field. This can be useful for tracking62 who last modified a particular row within a table.63 </p><p>64 To use, create a <code class="literal">BEFORE INSERT</code> and/or <code class="literal">UPDATE</code>65 trigger using this function. Specify a single trigger66 argument: the name of the text column to be modified.67 </p><p>68 There is an example in <code class="filename">insert_username.example</code>.69 </p></div><div class="sect2" id="CONTRIB-SPI-MODDATETIME"><div class="titlepage"><div><div><h3 class="title">F.41.4. moddatetime — Functions for Tracking Last Modification Time <a href="#CONTRIB-SPI-MODDATETIME" class="id_link">#</a></h3></div></div></div><p>70 <code class="function">moddatetime()</code> is a trigger that stores the current71 time into a <code class="type">timestamp</code> field. This can be useful for tracking72 the last modification time of a particular row within a table.73 </p><p>74 To use, create a <code class="literal">BEFORE UPDATE</code>75 trigger using this function. Specify a single trigger76 argument: the name of the column to be modified.77 The column must be of type <code class="type">timestamp</code> or <code class="type">timestamp with78 time zone</code>.79 </p><p>80 There is an example in <code class="filename">moddatetime.example</code>.81 </p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sepgsql.html" title="F.40. sepgsql — SELinux-, label-based mandatory access control (MAC) security module">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="contrib.html" title="Appendix F. Additional Supplied Modules and Extensions">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sslinfo.html" title="F.42. sslinfo — obtain client SSL information">Next</a></td></tr><tr><td width="40%" align="left" valign="top">F.40. sepgsql —82 SELinux-, label-based mandatory access control (MAC) security module </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"> F.42. sslinfo — obtain client SSL information</td></tr></table></div></body></html>