begriffs open source - ai-pg/blob - full-docs/txt/pltcl-dbaccess.txt

   1
   2 42.5. Database Access from PL/Tcl #
   3
   4    In this section, we follow the usual Tcl convention of using question
   5    marks, rather than brackets, to indicate an optional element in a
   6    syntax synopsis. The following commands are available to access the
   7    database from the body of a PL/Tcl function:
   8
   9    spi_exec ?-count n? ?-array name? command ?loop-body?
  10           Executes an SQL command given as a string. An error in the
  11           command causes an error to be raised. Otherwise, the return
  12           value of spi_exec is the number of rows processed (selected,
  13           inserted, updated, or deleted) by the command, or zero if the
  14           command is a utility statement. In addition, if the command is a
  15           SELECT statement, the values of the selected columns are placed
  16           in Tcl variables as described below.
  17
  18           The optional -count value tells spi_exec to stop once n rows
  19           have been retrieved, much as if the query included a LIMIT
  20           clause. If n is zero, the query is run to completion, the same
  21           as when -count is omitted.
  22
  23           If the command is a SELECT statement, the values of the result
  24           columns are placed into Tcl variables named after the columns.
  25           If the -array option is given, the column values are instead
  26           stored into elements of the named associative array, with the
  27           column names used as array indexes. In addition, the current row
  28           number within the result (counting from zero) is stored into the
  29           array element named “.tupno”, unless that name is in use as a
  30           column name in the result.
  31
  32           If the command is a SELECT statement and no loop-body script is
  33           given, then only the first row of results are stored into Tcl
  34           variables or array elements; remaining rows, if any, are
  35           ignored. No storing occurs if the query returns no rows. (This
  36           case can be detected by checking the result of spi_exec.) For
  37           example:
  38
  39 spi_exec "SELECT count(*) AS cnt FROM pg_proc"
  40
  41           will set the Tcl variable $cnt to the number of rows in the
  42           pg_proc system catalog.
  43
  44           If the optional loop-body argument is given, it is a piece of
  45           Tcl script that is executed once for each row in the query
  46           result. (loop-body is ignored if the given command is not a
  47           SELECT.) The values of the current row's columns are stored into
  48           Tcl variables or array elements before each iteration. For
  49           example:
  50
  51 spi_exec -array C "SELECT * FROM pg_class" {
  52     elog DEBUG "have table $C(relname)"
  53 }
  54
  55           will print a log message for every row of pg_class. This feature
  56           works similarly to other Tcl looping constructs; in particular
  57           continue and break work in the usual way inside the loop body.
  58
  59           If a column of a query result is null, the target variable for
  60           it is “unset” rather than being set.
  61
  62    spi_prepare query typelist
  63           Prepares and saves a query plan for later execution. The saved
  64           plan will be retained for the life of the current session.
  65
  66           The query can use parameters, that is, placeholders for values
  67           to be supplied whenever the plan is actually executed. In the
  68           query string, refer to parameters by the symbols $1 ... $n. If
  69           the query uses parameters, the names of the parameter types must
  70           be given as a Tcl list. (Write an empty list for typelist if no
  71           parameters are used.)
  72
  73           The return value from spi_prepare is a query ID to be used in
  74           subsequent calls to spi_execp. See spi_execp for an example.
  75
  76    spi_execp ?-count n? ?-array name? ?-nulls string? queryid ?value-list?
  77           ?loop-body?
  78           Executes a query previously prepared with spi_prepare. queryid
  79           is the ID returned by spi_prepare. If the query references
  80           parameters, a value-list must be supplied. This is a Tcl list of
  81           actual values for the parameters. The list must be the same
  82           length as the parameter type list previously given to
  83           spi_prepare. Omit value-list if the query has no parameters.
  84
  85           The optional value for -nulls is a string of spaces and 'n'
  86           characters telling spi_execp which of the parameters are null
  87           values. If given, it must have exactly the same length as the
  88           value-list. If it is not given, all the parameter values are
  89           nonnull.
  90
  91           Except for the way in which the query and its parameters are
  92           specified, spi_execp works just like spi_exec. The -count,
  93           -array, and loop-body options are the same, and so is the result
  94           value.
  95
  96           Here's an example of a PL/Tcl function using a prepared plan:
  97
  98 CREATE FUNCTION t1_count(integer, integer) RETURNS integer AS $$
  99     if {![ info exists GD(plan) ]} {
 100         # prepare the saved plan on the first call
 101         set GD(plan) [ spi_prepare \
 102                 "SELECT count(*) AS cnt FROM t1 WHERE num >= \$1 AND num <= \$2"
 103  \
 104                 [ list int4 int4 ] ]
 105     }
 106     spi_execp -count 1 $GD(plan) [ list $1 $2 ]
 107     return $cnt
 108 $$ LANGUAGE pltcl;
 109
 110           We need backslashes inside the query string given to spi_prepare
 111           to ensure that the $n markers will be passed through to
 112           spi_prepare as-is, and not replaced by Tcl variable
 113           substitution.
 114
 115    subtransaction command
 116           The Tcl script contained in command is executed within an SQL
 117           subtransaction. If the script returns an error, that entire
 118           subtransaction is rolled back before returning the error out to
 119           the surrounding Tcl code. See Section 42.9 for more details and
 120           an example.
 121
 122    quote string
 123           Doubles all occurrences of single quote and backslash characters
 124           in the given string. This can be used to safely quote strings
 125           that are to be inserted into SQL commands given to spi_exec or
 126           spi_prepare. For example, think about an SQL command string
 127           like:
 128
 129 "SELECT '$val' AS ret"
 130
 131           where the Tcl variable val actually contains doesn't. This would
 132           result in the final command string:
 133
 134 SELECT 'doesn't' AS ret
 135
 136           which would cause a parse error during spi_exec or spi_prepare.
 137           To work properly, the submitted command should contain:
 138
 139 SELECT 'doesn''t' AS ret
 140
 141           which can be formed in PL/Tcl using:
 142
 143 "SELECT '[ quote $val ]' AS ret"
 144
 145           One advantage of spi_execp is that you don't have to quote
 146           parameter values like this, since the parameters are never
 147           parsed as part of an SQL command string.
 148
 149    elog level msg
 150           Emits a log or error message. Possible levels are DEBUG, LOG,
 151           INFO, NOTICE, WARNING, ERROR, and FATAL. ERROR raises an error
 152           condition; if this is not trapped by the surrounding Tcl code,
 153           the error propagates out to the calling query, causing the
 154           current transaction or subtransaction to be aborted. This is
 155           effectively the same as the Tcl error command. FATAL aborts the
 156           transaction and causes the current session to shut down. (There
 157           is probably no good reason to use this error level in PL/Tcl
 158           functions, but it's provided for completeness.) The other levels
 159           only generate messages of different priority levels. Whether
 160           messages of a particular priority are reported to the client,
 161           written to the server log, or both is controlled by the
 162           log_min_messages and client_min_messages configuration
 163           variables. See Chapter 19 and Section 42.8 for more information.