summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/html/pltcl-dbaccess.html
diff options
context:
space:
mode:
authorDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
committerDaniel Baumann <daniel.baumann@progress-linux.org>2024-05-04 12:17:33 +0000
commit5e45211a64149b3c659b90ff2de6fa982a5a93ed (patch)
tree739caf8c461053357daa9f162bef34516c7bf452 /doc/src/sgml/html/pltcl-dbaccess.html
parentInitial commit. (diff)
downloadpostgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.tar.xz
postgresql-15-5e45211a64149b3c659b90ff2de6fa982a5a93ed.zip
Adding upstream version 15.5.upstream/15.5
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to 'doc/src/sgml/html/pltcl-dbaccess.html')
-rw-r--r--doc/src/sgml/html/pltcl-dbaccess.html193
1 files changed, 193 insertions, 0 deletions
diff --git a/doc/src/sgml/html/pltcl-dbaccess.html b/doc/src/sgml/html/pltcl-dbaccess.html
new file mode 100644
index 0000000..025d849
--- /dev/null
+++ b/doc/src/sgml/html/pltcl-dbaccess.html
@@ -0,0 +1,193 @@
+<?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.5. Database Access from 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-global.html" title="44.4. Global Data in PL/Tcl" /><link rel="next" href="pltcl-trigger.html" title="44.6. 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.5. Database Access from PL/Tcl</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="pltcl-global.html" title="44.4. Global Data in 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.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="pltcl-trigger.html" title="44.6. Trigger Functions in PL/Tcl">Next</a></td></tr></table><hr /></div><div class="sect1" id="PLTCL-DBACCESS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">44.5. Database Access from PL/Tcl</h2></div></div></div><p>
+ In this section, we follow the usual Tcl convention of using question
+ marks, rather than brackets, to indicate an optional element in a
+ syntax synopsis. The following commands are available to access
+ the database from the body of a PL/Tcl function:
+
+ </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal"><code class="function">spi_exec</code> ?<span class="optional">-count <em class="replaceable"><code>n</code></em></span>? ?<span class="optional">-array <em class="replaceable"><code>name</code></em></span>? <em class="replaceable"><code>command</code></em> ?<span class="optional"><em class="replaceable"><code>loop-body</code></em></span>?</code></span></dt><dd><p>
+ Executes an SQL command given as a string. An error in the command
+ causes an error to be raised. Otherwise, the return value of <code class="function">spi_exec</code>
+ is the number of rows processed (selected, inserted, updated, or
+ deleted) by the command, or zero if the command is a utility
+ statement. In addition, if the command is a <code class="command">SELECT</code> statement, the
+ values of the selected columns are placed in Tcl variables as
+ described below.
+ </p><p>
+ The optional <code class="literal">-count</code> value tells
+ <code class="function">spi_exec</code> to stop
+ once <em class="replaceable"><code>n</code></em> rows have been retrieved,
+ much as if the query included a <code class="literal">LIMIT</code> clause.
+ If <em class="replaceable"><code>n</code></em> is zero, the query is run to
+ completion, the same as when <code class="literal">-count</code> is omitted.
+ </p><p>
+ If the command is a <code class="command">SELECT</code> statement, the values of the
+ result columns are placed into Tcl variables named after the columns.
+ If the <code class="literal">-array</code> option is given, the column values are
+ instead stored into elements of the named associative array, with the
+ column names used as array indexes. In addition, the current row
+ number within the result (counting from zero) is stored into the array
+ element named <span class="quote">“<span class="quote"><code class="literal">.tupno</code></span>”</span>, unless that name is
+ in use as a column name in the result.
+ </p><p>
+ If the command is a <code class="command">SELECT</code> statement and no <em class="replaceable"><code>loop-body</code></em>
+ script is given, then only the first row of results are stored into
+ Tcl variables or array elements; remaining rows, if any, are ignored.
+ No storing occurs if the query returns no rows. (This case can be
+ detected by checking the result of <code class="function">spi_exec</code>.)
+ For example:
+</p><pre class="programlisting">
+spi_exec "SELECT count(*) AS cnt FROM pg_proc"
+</pre><p>
+ will set the Tcl variable <code class="literal">$cnt</code> to the number of rows in
+ the <code class="structname">pg_proc</code> system catalog.
+ </p><p>
+ If the optional <em class="replaceable"><code>loop-body</code></em> argument is given, it is
+ a piece of Tcl script that is executed once for each row in the
+ query result. (<em class="replaceable"><code>loop-body</code></em> is ignored if the given
+ command is not a <code class="command">SELECT</code>.)
+ The values of the current row's columns
+ are stored into Tcl variables or array elements before each iteration.
+ For example:
+</p><pre class="programlisting">
+spi_exec -array C "SELECT * FROM pg_class" {
+ elog DEBUG "have table $C(relname)"
+}
+</pre><p>
+ will print a log message for every row of <code class="literal">pg_class</code>. This
+ feature works similarly to other Tcl looping constructs; in
+ particular <code class="literal">continue</code> and <code class="literal">break</code> work in the
+ usual way inside the loop body.
+ </p><p>
+ If a column of a query result is null, the target
+ variable for it is <span class="quote">“<span class="quote">unset</span>”</span> rather than being set.
+ </p></dd><dt><span class="term"><code class="function">spi_prepare</code> <em class="replaceable"><code>query</code></em> <em class="replaceable"><code>typelist</code></em></span></dt><dd><p>
+ Prepares and saves a query plan for later execution. The
+ saved plan will be retained for the life of the current
+ session.<a id="id-1.8.9.9.2.1.2.2.1.1" class="indexterm"></a>
+ </p><p>
+ The query can use parameters, that is, placeholders for
+ values to be supplied whenever the plan is actually executed.
+ In the query string, refer to parameters
+ by the symbols <code class="literal">$1</code> ... <code class="literal">$<em class="replaceable"><code>n</code></em></code>.
+ If the query uses parameters, the names of the parameter types
+ must be given as a Tcl list. (Write an empty list for
+ <em class="replaceable"><code>typelist</code></em> if no parameters are used.)
+ </p><p>
+ The return value from <code class="function">spi_prepare</code> is a query ID
+ to be used in subsequent calls to <code class="function">spi_execp</code>. See
+ <code class="function">spi_execp</code> for an example.
+ </p></dd><dt><span class="term"><code class="literal"><code class="function">spi_execp</code> ?<span class="optional">-count <em class="replaceable"><code>n</code></em></span>? ?<span class="optional">-array <em class="replaceable"><code>name</code></em></span>? ?<span class="optional">-nulls <em class="replaceable"><code>string</code></em></span>? <em class="replaceable"><code>queryid</code></em> ?<span class="optional"><em class="replaceable"><code>value-list</code></em></span>? ?<span class="optional"><em class="replaceable"><code>loop-body</code></em></span>?</code></span></dt><dd><p>
+ Executes a query previously prepared with <code class="function">spi_prepare</code>.
+ <em class="replaceable"><code>queryid</code></em> is the ID returned by
+ <code class="function">spi_prepare</code>. If the query references parameters,
+ a <em class="replaceable"><code>value-list</code></em> must be supplied. This
+ is a Tcl list of actual values for the parameters. The list must be
+ the same length as the parameter type list previously given to
+ <code class="function">spi_prepare</code>. Omit <em class="replaceable"><code>value-list</code></em>
+ if the query has no parameters.
+ </p><p>
+ The optional value for <code class="literal">-nulls</code> is a string of spaces and
+ <code class="literal">'n'</code> characters telling <code class="function">spi_execp</code>
+ which of the parameters are null values. If given, it must have exactly the
+ same length as the <em class="replaceable"><code>value-list</code></em>. If it
+ is not given, all the parameter values are nonnull.
+ </p><p>
+ Except for the way in which the query and its parameters are specified,
+ <code class="function">spi_execp</code> works just like <code class="function">spi_exec</code>.
+ The <code class="literal">-count</code>, <code class="literal">-array</code>, and
+ <em class="replaceable"><code>loop-body</code></em> options are the same,
+ and so is the result value.
+ </p><p>
+ Here's an example of a PL/Tcl function using a prepared plan:
+
+</p><pre class="programlisting">
+CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
+ if {![ info exists GD(plan) ]} {
+ # prepare the saved plan on the first call
+ set GD(plan) [ spi_prepare \
+ "SELECT count(*) AS cnt FROM t1 WHERE num &gt;= \$1 AND num &lt;= \$2" \
+ [ list int4 int4 ] ]
+ }
+ spi_execp -count 1 $GD(plan) [ list $1 $2 ]
+ return $cnt
+$$ LANGUAGE pltcl;
+</pre><p>
+
+ We need backslashes inside the query string given to
+ <code class="function">spi_prepare</code> to ensure that the
+ <code class="literal">$<em class="replaceable"><code>n</code></em></code> markers will be passed
+ through to <code class="function">spi_prepare</code> as-is, and not replaced by Tcl
+ variable substitution.
+
+ </p></dd><dt><span class="term"><code class="function">subtransaction</code> <em class="replaceable"><code>command</code></em></span></dt><dd><p>
+ The Tcl script contained in <em class="replaceable"><code>command</code></em> is
+ executed within an SQL subtransaction. If the script returns an
+ error, that entire subtransaction is rolled back before returning the
+ error out to the surrounding Tcl code.
+ See <a class="xref" href="pltcl-subtransactions.html" title="44.9. Explicit Subtransactions in PL/Tcl">Section 44.9</a> for more details and an
+ example.
+ </p></dd><dt><span class="term"><code class="function">quote</code> <em class="replaceable"><code>string</code></em></span></dt><dd><p>
+ Doubles all occurrences of single quote and backslash characters
+ in the given string. This can be used to safely quote strings
+ that are to be inserted into SQL commands given
+ to <code class="function">spi_exec</code> or
+ <code class="function">spi_prepare</code>.
+ For example, think about an SQL command string like:
+
+</p><pre class="programlisting">
+"SELECT '$val' AS ret"
+</pre><p>
+
+ where the Tcl variable <code class="literal">val</code> actually contains
+ <code class="literal">doesn't</code>. This would result
+ in the final command string:
+
+</p><pre class="programlisting">
+SELECT 'doesn't' AS ret
+</pre><p>
+
+ which would cause a parse error during
+ <code class="function">spi_exec</code> or
+ <code class="function">spi_prepare</code>.
+ To work properly, the submitted command should contain:
+
+</p><pre class="programlisting">
+SELECT 'doesn''t' AS ret
+</pre><p>
+
+ which can be formed in PL/Tcl using:
+
+</p><pre class="programlisting">
+"SELECT '[ quote $val ]' AS ret"
+</pre><p>
+
+ One advantage of <code class="function">spi_execp</code> is that you don't
+ have to quote parameter values like this, since the parameters are never
+ parsed as part of an SQL command string.
+ </p></dd><dt><span class="term">
+ <code class="function">elog</code> <em class="replaceable"><code>level</code></em> <em class="replaceable"><code>msg</code></em>
+ <a id="id-1.8.9.9.2.1.6.1.4" class="indexterm"></a>
+ </span></dt><dd><p>
+ Emits a log or error message. Possible levels are
+ <code class="literal">DEBUG</code>, <code class="literal">LOG</code>, <code class="literal">INFO</code>,
+ <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, <code class="literal">ERROR</code>, and
+ <code class="literal">FATAL</code>. <code class="literal">ERROR</code>
+ raises an error condition; if this is not trapped by the surrounding
+ Tcl code, the error propagates out to the calling query, causing
+ the current transaction or subtransaction to be aborted. This
+ is effectively the same as the Tcl <code class="literal">error</code> command.
+ <code class="literal">FATAL</code> aborts the transaction and causes the current
+ session to shut down. (There is probably no good reason to use
+ this error level in PL/Tcl functions, but it's provided for
+ completeness.) The other levels only generate messages of different
+ priority levels.
+ Whether messages of a particular priority are reported to the client,
+ written to the server log, or both is controlled by the
+ <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> and
+ <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> configuration
+ variables. See <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>
+ and <a class="xref" href="pltcl-error-handling.html" title="44.8. Error Handling in PL/Tcl">Section 44.8</a>
+ for more information.
+ </p></dd></dl></div><p>
+ </p></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="pltcl-global.html" title="44.4. Global Data in 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-trigger.html" title="44.6. Trigger Functions in PL/Tcl">Next</a></td></tr><tr><td width="40%" align="left" valign="top">44.4. Global Data in PL/Tcl </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 44.6. Trigger Functions in PL/Tcl</td></tr></table></div></body></html> \ No newline at end of file