diff options
Diffstat (limited to 'doc/src/sgml/html/pltcl-dbaccess.html')
| -rw-r--r-- | doc/src/sgml/html/pltcl-dbaccess.html | 191 |
1 files changed, 191 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..2fdc36d --- /dev/null +++ b/doc/src/sgml/html/pltcl-dbaccess.html @@ -0,0 +1,191 @@ +<?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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 14.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></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> the maximum number of rows + to process in the command. The effect of this is comparable to + setting up a query as a cursor and then saying <code class="literal">FETCH <em class="replaceable"><code>n</code></em></code>. + </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 >= \$1 AND num <= \$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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 14.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 |