diff options
| author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
|---|---|---|
| committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-16 19:46:48 +0000 |
| commit | 311bcfc6b3acdd6fd152798c7f287ddf74fa2a98 (patch) | |
| tree | 0ec307299b1dada3701e42f4ca6eda57d708261e /doc/src/sgml/html/pltcl-trigger.html | |
| parent | Initial commit. (diff) | |
| download | postgresql-15-upstream.tar.xz postgresql-15-upstream.zip | |
Adding upstream version 15.4.upstream/15.4upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/pltcl-trigger.html')
| -rw-r--r-- | doc/src/sgml/html/pltcl-trigger.html | 115 |
1 files changed, 115 insertions, 0 deletions
diff --git a/doc/src/sgml/html/pltcl-trigger.html b/doc/src/sgml/html/pltcl-trigger.html new file mode 100644 index 0000000..6cb9cf8 --- /dev/null +++ b/doc/src/sgml/html/pltcl-trigger.html @@ -0,0 +1,115 @@ +<?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>44.6. Trigger Functions in PL/Tcl</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="pltcl-dbaccess.html" title="44.5. Database Access from PL/Tcl" /><link rel="next" href="pltcl-event-trigger.html" title="44.7. Event Trigger Functions in PL/Tcl" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">44.6. Trigger Functions in PL/Tcl</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pltcl-dbaccess.html" title="44.5. Database Access from PL/Tcl">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="pltcl.html" title="Chapter 44. PL/Tcl — Tcl Procedural Language">Up</a></td><th width="60%" align="center">Chapter 44. PL/Tcl — Tcl Procedural Language</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pltcl-event-trigger.html" title="44.7. Event Trigger Functions in PL/Tcl">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLTCL-TRIGGER"><div class="titlepage"><div><div><h2 class="title" style="clear: both">44.6. Trigger Functions in PL/Tcl</h2></div></div></div><a id="id-1.8.9.10.2" class="indexterm"></a><p> + Trigger functions can be written in PL/Tcl. + <span class="productname">PostgreSQL</span> requires that a function that is to be called + as a trigger must be declared as a function with no arguments + and a return type of <code class="literal">trigger</code>. + </p><p> + The information from the trigger manager is passed to the function body + in the following variables: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="varname">$TG_name</code></span></dt><dd><p> + The name of the trigger from the <code class="command">CREATE TRIGGER</code> statement. + </p></dd><dt><span class="term"><code class="varname">$TG_relid</code></span></dt><dd><p> + The object ID of the table that caused the trigger function + to be invoked. + </p></dd><dt><span class="term"><code class="varname">$TG_table_name</code></span></dt><dd><p> + The name of the table that caused the trigger function + to be invoked. + </p></dd><dt><span class="term"><code class="varname">$TG_table_schema</code></span></dt><dd><p> + The schema of the table that caused the trigger function + to be invoked. + </p></dd><dt><span class="term"><code class="varname">$TG_relatts</code></span></dt><dd><p> + A Tcl list of the table column names, prefixed with an empty list + element. So looking up a column name in the list with <span class="application">Tcl</span>'s + <code class="function">lsearch</code> command returns the element's number starting + with 1 for the first column, the same way the columns are customarily + numbered in <span class="productname">PostgreSQL</span>. (Empty list + elements also appear in the positions of columns that have been + dropped, so that the attribute numbering is correct for columns + to their right.) + </p></dd><dt><span class="term"><code class="varname">$TG_when</code></span></dt><dd><p> + The string <code class="literal">BEFORE</code>, <code class="literal">AFTER</code>, or + <code class="literal">INSTEAD OF</code>, depending on the type of trigger event. + </p></dd><dt><span class="term"><code class="varname">$TG_level</code></span></dt><dd><p> + The string <code class="literal">ROW</code> or <code class="literal">STATEMENT</code> depending on the + type of trigger event. + </p></dd><dt><span class="term"><code class="varname">$TG_op</code></span></dt><dd><p> + The string <code class="literal">INSERT</code>, <code class="literal">UPDATE</code>, + <code class="literal">DELETE</code>, or <code class="literal">TRUNCATE</code> depending on the type of + trigger event. + </p></dd><dt><span class="term"><code class="varname">$NEW</code></span></dt><dd><p> + An associative array containing the values of the new table + row for <code class="command">INSERT</code> or <code class="command">UPDATE</code> actions, or + empty for <code class="command">DELETE</code>. The array is indexed by column + name. Columns that are null will not appear in the array. + This is not set for statement-level triggers. + </p></dd><dt><span class="term"><code class="varname">$OLD</code></span></dt><dd><p> + An associative array containing the values of the old table + row for <code class="command">UPDATE</code> or <code class="command">DELETE</code> actions, or + empty for <code class="command">INSERT</code>. The array is indexed by column + name. Columns that are null will not appear in the array. + This is not set for statement-level triggers. + </p></dd><dt><span class="term"><code class="varname">$args</code></span></dt><dd><p> + A Tcl list of the arguments to the function as given in the + <code class="command">CREATE TRIGGER</code> statement. These arguments are also accessible as + <code class="literal">$1</code> ... <code class="literal">$<em class="replaceable"><code>n</code></em></code> in the function body. + </p></dd></dl></div><p> + </p><p> + The return value from a trigger function can be one of the strings + <code class="literal">OK</code> or <code class="literal">SKIP</code>, or a list of column name/value pairs. + If the return value is <code class="literal">OK</code>, + the operation (<code class="command">INSERT</code>/<code class="command">UPDATE</code>/<code class="command">DELETE</code>) + that fired the trigger will proceed + normally. <code class="literal">SKIP</code> tells the trigger manager to silently suppress + the operation for this row. If a list is returned, it tells PL/Tcl to + return a modified row to the trigger manager; the contents of the + modified row are specified by the column names and values in the list. + Any columns not mentioned in the list are set to null. + Returning a modified row is only meaningful + for row-level <code class="literal">BEFORE</code> <code class="command">INSERT</code> or <code class="command">UPDATE</code> + triggers, for which the modified row will be inserted instead of the one + given in <code class="varname">$NEW</code>; or for row-level <code class="literal">INSTEAD OF</code> + <code class="command">INSERT</code> or <code class="command">UPDATE</code> triggers where the returned row + is used as the source data for <code class="command">INSERT RETURNING</code> or + <code class="command">UPDATE RETURNING</code> clauses. + In row-level <code class="literal">BEFORE</code> <code class="command">DELETE</code> or <code class="literal">INSTEAD + OF</code> <code class="command">DELETE</code> triggers, returning a modified row has the same + effect as returning <code class="literal">OK</code>, that is the operation proceeds. + The trigger return value is ignored for all other types of triggers. + </p><div class="tip"><h3 class="title">Tip</h3><p> + The result list can be made from an array representation of the + modified tuple with the <code class="literal">array get</code> Tcl command. + </p></div><p> + Here's a little example trigger function that forces an integer value + in a table to keep track of the number of updates that are performed on the + row. For new rows inserted, the value is initialized to 0 and then + incremented on every update operation. + +</p><pre class="programlisting"> +CREATE FUNCTION trigfunc_modcount() RETURNS trigger AS $$ + switch $TG_op { + INSERT { + set NEW($1) 0 + } + UPDATE { + set NEW($1) $OLD($1) + incr NEW($1) + } + default { + return OK + } + } + return [array get NEW] +$$ LANGUAGE pltcl; + +CREATE TABLE mytab (num integer, description text, modcnt integer); + +CREATE TRIGGER trig_mytab_modcount BEFORE INSERT OR UPDATE ON mytab + FOR EACH ROW EXECUTE FUNCTION trigfunc_modcount('modcnt'); +</pre><p> + + Notice that the trigger function itself does not know the column + name; that's supplied from the trigger arguments. This lets the + trigger function be reused with different tables. + </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pltcl-dbaccess.html" title="44.5. Database Access from PL/Tcl">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="pltcl.html" title="Chapter 44. PL/Tcl — Tcl Procedural Language">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="pltcl-event-trigger.html" title="44.7. Event Trigger Functions in PL/Tcl">Next</a></td></tr><tr><td width="40%" align="left" valign="top">44.5. Database Access from PL/Tcl </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 44.7. Event Trigger Functions in PL/Tcl</td></tr></table></div></body></html>
\ No newline at end of file |