libpgtcl.sgml

PostgreSQL7.4.6 for Linux

<refentry ID="PGTCL-PGRESULT">
 <refmeta>
  <refentrytitle>pg_result</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>pg_result</refname>
  <refpurpose>get information about a command result</refpurpose>
  <indexterm ID="IX-PGTCL-PGRESULT-2"><primary>pg_result</primary></indexterm>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
pg_result <parameter>resultHandle</parameter> <parameter>resultOption</parameter>
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>pg_result</function> returns information about a command
   result created by a prior <function>pg_exec</function>.
  </para>

  <para>
   You can keep a command result around for as long as you need it,
   but when you are done with it, be sure to free it by executing
   <function>pg_result -clear</function>.  Otherwise, you have a
   memory leak, and <application>pgtcl</> will eventually start
   complaining that you have created too many command result objects.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><parameter>resultHandle</parameter></term>
    <listitem>
     <para>
      The handle of the command result.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>resultOption</parameter></term>
    <listitem>
     <para>
      One of the following options, specifying which piece of result
      information to return:

      <variablelist>
       <varlistentry>
        <term><option>-status</option></term>
        <listitem>
         <para>
          The status of the result.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-error</option></term>
        <listitem>
         <para>
          The error message, if the status indicates an error,
          otherwise an empty string.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-conn</option></term>
        <listitem>
         <para>
          The connection that produced the result.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-oid</option></term>
        <listitem>
         <para>
          If the command was an <command>INSERT</command>, the OID of
          the inserted row, otherwise 0.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-numTuples</option></term>
        <listitem>
         <para>
          The number of rows (tuples) returned by the query.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-cmdTuples</option></term>
        <listitem>
         <para>
          The number of rows (tuples) affected by the command.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-numAttrs</option></term>
        <listitem>
         <para>
          The number of columns (attributes) in each row.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-assign <parameter>arrayName</parameter></option></term>
        <listitem>
         <para>
          Assign the results to an array, using subscripts of the form
          <literal>(rowNumber, columnName)</literal>.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-assignbyidx <parameter>arrayName</> <optional role="tcl"><parameter>appendstr</></optional></option></term>
        <listitem>
         <para>
          Assign the results to an array using the values of the
          first column and the names of the remaining column as keys.
          If <parameter>appendstr</> is given then it is appended to
          each key.  In short, all but the first column of each row
          are stored into the array, using subscripts of the form
          <literal>(firstColumnValue, columnNameAppendStr)</literal>.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-getTuple <parameter>rowNumber</parameter></option></term>
        <listitem>
         <para>
          Returns the columns of the indicated row in a list.  Row
          numbers start at zero.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-tupleArray <parameter>rowNumber</> <parameter>arrayName</></option></term>
        <listitem>
         <para>
          Stores the columns of the row in array
          <parameter>arrayName</parameter>, indexed by column names.
          Row numbers start at zero.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-attributes</option></term>
        <listitem>
         <para>
          Returns a list of the names of the columns in the result.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-lAttributes</option></term>
        <listitem>
         <para>
          Returns a list of sublists, <literal>{name typeOid
          typeSize}</literal> for each column.
         </para>
        </listitem>
       </varlistentry>

       <varlistentry>
        <term><option>-clear</option></term>
        <listitem>
         <para>
          Clear the command result object.
         </para>
        </listitem>
       </varlistentry>
      </variablelist>
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <para>
   The result depends on the selected option, as described above.
  </para>
 </refsect1>
</refentry>


<refentry ID="PGTCL-PGSELECT">
 <refmeta>
  <refentrytitle>pg_select</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>pg_select</refname>
  <refpurpose>loop over the result of a query</refpurpose>
  <indexterm ID="IX-PGTCL-PGSELECT-2"><primary>pg_select</primary></indexterm>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
pg_select <parameter>conn</parameter> <parameter>commandString</parameter> <parameter>arrayVar</parameter> <parameter>procedure</parameter>
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>pg_select</function> submits a query
   (<command>SELECT</command> statement) to the
   <productname>PostgreSQL</productname> server and executes a given
   chunk of code for each row in the result.  The
   <parameter>commandString</parameter> must be a
   <command>SELECT</command> statement; anything else returns an
   error.  The <parameter>arrayVar</parameter> variable is an array
   name used in the loop.  For each row,
   <parameter>arrayVar</parameter> is filled in with the row values,
   using the column names as the array indices.  Then the
   <parameter>procedure</parameter> is executed.
  </para>

  <para>
   In addition to the column values, the following special entries are
   made in the array:

   <variablelist>
    <varlistentry>
     <term><literal>.headers</></term>
     <listitem>
      <para>
       A list of the column names returned by the query.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>.numcols</></term>
     <listitem>
      <para>
       The number of columns returned by the query.
      </para>
     </listitem>
    </varlistentry>

    <varlistentry>
     <term><literal>.tupno</></term>
     <listitem>
      <para>
       The current row number, starting at zero and incrementing for
       each iteration of the loop body.
      </para>
     </listitem>
    </varlistentry>
   </variablelist>
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><parameter>conn</parameter></term>
    <listitem>
     <para>
      The handle of the connection on which to execute the query.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>commandString</parameter></term>
    <listitem>
     <para>
      The SQL query to execute.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>arrayVar</parameter></term>
    <listitem>
     <para>
      An array variable for returned rows.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>procedure</parameter></term>
    <listitem>
     <para>
      The procedure to run for each returned row.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Return Value</title>
  <para>
   None
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   This examples assumes that the table <classname>table1</> has
   columns <structfield>control</> and <structfield>name</> (and
   perhaps others):
<programlisting>
pg_select $pgconn "SELECT * FROM table1;" array {
    puts [format "%5d %s" $array(control) $array(name)]
}
</programlisting>
  </para>
 </refsect1>
</refentry>


<refentry ID="PGTCL-PGEXECUTE">
 <refmeta>
  <refentrytitle>pg_execute</refentrytitle>
 </refmeta>

 <refnamediv>
  <refname>pg_execute</refname>
  <refpurpose>send a query and optionally loop over the results</refpurpose>
  <indexterm ID="IX-PGTCL-PGEXECUTE-2"><primary>pg_execute</primary></indexterm>
 </refnamediv>

 <refsynopsisdiv>
<synopsis>
pg_execute <optional role="tcl">-array <parameter>arrayVar</parameter></optional> <optional role="tcl">-oid <parameter>oidVar</parameter></optional> <parameter>conn</parameter> <parameter>commandString</parameter> <optional role="tcl"><parameter>procedure</parameter></optional>
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <function>pg_execute</function> submits a command to the
   <productname>PostgreSQL</> server.
  </para>

  <para>
   If the command is not a <command>SELECT</command> statement, the
   number of rows affected by the command is returned.  If the command
   is an <command>INSERT</command> statement and a single row is
   inserted, the OID of the inserted row is stored in the variable
   <parameter>oidVar</> if the optional <parameter>-oid</parameter>
   argument is supplied.
  </para>

  <para>
   If the command is a <command>SELECT</command> statement, then, for
   each row in the result, the row values are stored in the
   <parameter>arrayVar</parameter> variable, if supplied, using the
   column names as the array indices, else in variables named by the
   column names, and then the optional
   <parameter>procedure</parameter> is executed if supplied.
   (Omitting the <parameter>procedure</parameter> probably makes sense
   only if the query will return a single row.)  The number of rows
   selected is returned.
  </para>

  <para>
   The <parameter>procedure</parameter> can use the Tcl commands
   <literal>break</literal>, <literal>continue</literal>, and
   <literal>return</literal> with the expected behavior.  Note that if
   the <parameter>procedure</parameter> executes
   <literal>return</literal>, then <function>pg_execute</function>
   does not return the number of affected rows.
  </para>

  <para>
   <function>pg_execute</function> is a newer function which provides
   a superset of the features of <function>pg_select</function> and
   can replace <function>pg_exec</function> in many cases where access
   to the result handle is not needed.
  </para>

  <para>
   For server-handled errors, <function>pg_execute</function> will
   throw a Tcl error and return a two-element list.  The first element
   is an error code, such as <literal>PGRES_FATAL_ERROR</literal>, and
   the second element is the server error text.  For more serious
   errors, such as failure to communicate with the server,
   <function>pg_execute</function> will throw a Tcl error and return
   just the error message text.
  </para>
 </refsect1>

 <refsect1>
  <title>Arguments</title>

  <variablelist>
   <varlistentry>
    <term><option>-array <parameter>arrayVar</parameter></option></term>
    <listitem>
     <para>
      Specifies the name of an array variable where result rows are
      stored, indexed by the column names.  This is ignored if
      <parameter>commandString</> is not a <command>SELECT</>
      statement.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><option>-oid <parameter>oidVar</parameter></option></term>
    <listitem>
     <para>
      Specifies the name of a variable into which the OID from an
      <command>INSERT</command> statement will be stored.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>conn</parameter></term>
    <listitem>
     <para>
      The handle of the connection on which to execute the command.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>commandString</parameter></term>
    <listitem>
     <para>
      The SQL command to execute.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><parameter>procedure</parameter></term>
    <listitem>
     <para>
      Optional procedure to execute for each result row of a
      <command>SELECT</command> statement.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1>
  <title>Return Value</title>

  <para>
   The number of rows affected or returned by the command.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   In the following examples, error checking with