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>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 16.3 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 <a href="#PLTCL-DBACCESS" class="id_link">#</a></h2></div></div></div><p>3 In this section, we follow the usual Tcl convention of using question4 marks, rather than brackets, to indicate an optional element in a5 syntax synopsis. The following commands are available to access6 the database from the body of a PL/Tcl function:7 8 </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>9 Executes an SQL command given as a string. An error in the command10 causes an error to be raised. Otherwise, the return value of <code class="function">spi_exec</code>11 is the number of rows processed (selected, inserted, updated, or12 deleted) by the command, or zero if the command is a utility13 statement. In addition, if the command is a <code class="command">SELECT</code> statement, the14 values of the selected columns are placed in Tcl variables as15 described below.16 </p><p>17 The optional <code class="literal">-count</code> value tells18 <code class="function">spi_exec</code> to stop19 once <em class="replaceable"><code>n</code></em> rows have been retrieved,20 much as if the query included a <code class="literal">LIMIT</code> clause.21 If <em class="replaceable"><code>n</code></em> is zero, the query is run to22 completion, the same as when <code class="literal">-count</code> is omitted.23 </p><p>24 If the command is a <code class="command">SELECT</code> statement, the values of the25 result columns are placed into Tcl variables named after the columns.26 If the <code class="literal">-array</code> option is given, the column values are27 instead stored into elements of the named associative array, with the28 column names used as array indexes. In addition, the current row29 number within the result (counting from zero) is stored into the array30 element named <span class="quote">“<span class="quote"><code class="literal">.tupno</code></span>”</span>, unless that name is31 in use as a column name in the result.32 </p><p>33 If the command is a <code class="command">SELECT</code> statement and no <em class="replaceable"><code>loop-body</code></em>34 script is given, then only the first row of results are stored into35 Tcl variables or array elements; remaining rows, if any, are ignored.36 No storing occurs if the query returns no rows. (This case can be37 detected by checking the result of <code class="function">spi_exec</code>.)38 For example:39</p><pre class="programlisting">40spi_exec "SELECT count(*) AS cnt FROM pg_proc"41</pre><p>42 will set the Tcl variable <code class="literal">$cnt</code> to the number of rows in43 the <code class="structname">pg_proc</code> system catalog.44 </p><p>45 If the optional <em class="replaceable"><code>loop-body</code></em> argument is given, it is46 a piece of Tcl script that is executed once for each row in the47 query result. (<em class="replaceable"><code>loop-body</code></em> is ignored if the given48 command is not a <code class="command">SELECT</code>.)49 The values of the current row's columns50 are stored into Tcl variables or array elements before each iteration.51 For example:52</p><pre class="programlisting">53spi_exec -array C "SELECT * FROM pg_class" {54 elog DEBUG "have table $C(relname)"55}56</pre><p>57 will print a log message for every row of <code class="literal">pg_class</code>. This58 feature works similarly to other Tcl looping constructs; in59 particular <code class="literal">continue</code> and <code class="literal">break</code> work in the60 usual way inside the loop body.61 </p><p>62 If a column of a query result is null, the target63 variable for it is <span class="quote">“<span class="quote">unset</span>”</span> rather than being set.64 </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>65 Prepares and saves a query plan for later execution. The66 saved plan will be retained for the life of the current67 session.<a id="id-1.8.9.9.2.1.2.2.1.1" class="indexterm"></a>68 </p><p>69 The query can use parameters, that is, placeholders for70 values to be supplied whenever the plan is actually executed.71 In the query string, refer to parameters72 by the symbols <code class="literal">$1</code> ... <code class="literal">$<em class="replaceable"><code>n</code></em></code>.73 If the query uses parameters, the names of the parameter types74 must be given as a Tcl list. (Write an empty list for75 <em class="replaceable"><code>typelist</code></em> if no parameters are used.)76 </p><p>77 The return value from <code class="function">spi_prepare</code> is a query ID78 to be used in subsequent calls to <code class="function">spi_execp</code>. See79 <code class="function">spi_execp</code> for an example.80 </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>81 Executes a query previously prepared with <code class="function">spi_prepare</code>.82 <em class="replaceable"><code>queryid</code></em> is the ID returned by83 <code class="function">spi_prepare</code>. If the query references parameters,84 a <em class="replaceable"><code>value-list</code></em> must be supplied. This85 is a Tcl list of actual values for the parameters. The list must be86 the same length as the parameter type list previously given to87 <code class="function">spi_prepare</code>. Omit <em class="replaceable"><code>value-list</code></em>88 if the query has no parameters.89 </p><p>90 The optional value for <code class="literal">-nulls</code> is a string of spaces and91 <code class="literal">'n'</code> characters telling <code class="function">spi_execp</code>92 which of the parameters are null values. If given, it must have exactly the93 same length as the <em class="replaceable"><code>value-list</code></em>. If it94 is not given, all the parameter values are nonnull.95 </p><p>96 Except for the way in which the query and its parameters are specified,97 <code class="function">spi_execp</code> works just like <code class="function">spi_exec</code>.98 The <code class="literal">-count</code>, <code class="literal">-array</code>, and99 <em class="replaceable"><code>loop-body</code></em> options are the same,100 and so is the result value.101 </p><p>102 Here's an example of a PL/Tcl function using a prepared plan:103 104</p><pre class="programlisting">105CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$106 if {![ info exists GD(plan) ]} {107 # prepare the saved plan on the first call108 set GD(plan) [ spi_prepare \109 "SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2" \110 [ list int4 int4 ] ]111 }112 spi_execp -count 1 $GD(plan) [ list $1 $2 ]113 return $cnt114$$ LANGUAGE pltcl;115</pre><p>116 117 We need backslashes inside the query string given to118 <code class="function">spi_prepare</code> to ensure that the119 <code class="literal">$<em class="replaceable"><code>n</code></em></code> markers will be passed120 through to <code class="function">spi_prepare</code> as-is, and not replaced by Tcl121 variable substitution.122 123 </p></dd><dt><span class="term"><code class="function">subtransaction</code> <em class="replaceable"><code>command</code></em></span></dt><dd><p>124 The Tcl script contained in <em class="replaceable"><code>command</code></em> is125 executed within an SQL subtransaction. If the script returns an126 error, that entire subtransaction is rolled back before returning the127 error out to the surrounding Tcl code.128 See <a class="xref" href="pltcl-subtransactions.html" title="44.9. Explicit Subtransactions in PL/Tcl">Section 44.9</a> for more details and an129 example.130 </p></dd><dt><span class="term"><code class="function">quote</code> <em class="replaceable"><code>string</code></em></span></dt><dd><p>131 Doubles all occurrences of single quote and backslash characters132 in the given string. This can be used to safely quote strings133 that are to be inserted into SQL commands given134 to <code class="function">spi_exec</code> or135 <code class="function">spi_prepare</code>.136 For example, think about an SQL command string like:137 138</p><pre class="programlisting">139"SELECT '$val' AS ret"140</pre><p>141 142 where the Tcl variable <code class="literal">val</code> actually contains143 <code class="literal">doesn't</code>. This would result144 in the final command string:145 146</p><pre class="programlisting">147SELECT 'doesn't' AS ret148</pre><p>149 150 which would cause a parse error during151 <code class="function">spi_exec</code> or152 <code class="function">spi_prepare</code>.153 To work properly, the submitted command should contain:154 155</p><pre class="programlisting">156SELECT 'doesn''t' AS ret157</pre><p>158 159 which can be formed in PL/Tcl using:160 161</p><pre class="programlisting">162"SELECT '[ quote $val ]' AS ret"163</pre><p>164 165 One advantage of <code class="function">spi_execp</code> is that you don't166 have to quote parameter values like this, since the parameters are never167 parsed as part of an SQL command string.168 </p></dd><dt><span class="term">169 <code class="function">elog</code> <em class="replaceable"><code>level</code></em> <em class="replaceable"><code>msg</code></em>170 <a id="id-1.8.9.9.2.1.6.1.4" class="indexterm"></a>171 </span></dt><dd><p>172 Emits a log or error message. Possible levels are173 <code class="literal">DEBUG</code>, <code class="literal">LOG</code>, <code class="literal">INFO</code>,174 <code class="literal">NOTICE</code>, <code class="literal">WARNING</code>, <code class="literal">ERROR</code>, and175 <code class="literal">FATAL</code>. <code class="literal">ERROR</code>176 raises an error condition; if this is not trapped by the surrounding177 Tcl code, the error propagates out to the calling query, causing178 the current transaction or subtransaction to be aborted. This179 is effectively the same as the Tcl <code class="literal">error</code> command.180 <code class="literal">FATAL</code> aborts the transaction and causes the current181 session to shut down. (There is probably no good reason to use182 this error level in PL/Tcl functions, but it's provided for183 completeness.) The other levels only generate messages of different184 priority levels.185 Whether messages of a particular priority are reported to the client,186 written to the server log, or both is controlled by the187 <a class="xref" href="runtime-config-logging.html#GUC-LOG-MIN-MESSAGES">log_min_messages</a> and188 <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> configuration189 variables. See <a class="xref" href="runtime-config.html" title="Chapter 20. Server Configuration">Chapter 20</a>190 and <a class="xref" href="pltcl-error-handling.html" title="44.8. Error Handling in PL/Tcl">Section 44.8</a>191 for more information.192 </p></dd></dl></div><p>193 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 44.6. Trigger Functions in PL/Tcl</td></tr></table></div></body></html>