File: pgtcl-pgexecute.html

package info (click to toggle)
pgtcl 1%3A1.9%2Bgit20120911-3
links: PTS
area: main
in suites: jessie, jessie-kfreebsd
size: 2,240 kB
ctags: 315
sloc: ansic: 4,475; tcl: 634; sh: 149; makefile: 36; sql: 11
file content (496 lines) | stat: -rw-r--r-- 7,719 bytes
parent folder | download | duplicates (2)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<HTML
><HEAD
><TITLE
>pg_execute</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL Tcl Interface Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="pgtcl Command Reference"
HREF="libpgtcl-ref.html"><LINK
REL="PREVIOUS"
TITLE="pg_select"
HREF="pgtcl-pgselect.html"><LINK
REL="NEXT"
TITLE="pg_listen"
HREF="pgtcl-pglisten.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
NAME="creation"
CONTENT="2005-11-27T13:24:31"></HEAD
><BODY
CLASS="REFENTRY"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL Tcl Interface Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="pgtcl-pgselect.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="pgtcl-pgselect.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="pgtcl-pglisten.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="pgtcl-pglisten.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="PGTCL-PGEXECUTE"
></A
>pg_execute</H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN1001"
></A
><H2
>Name</H2
>pg_execute&nbsp;--&nbsp;send a query and optionally loop over the results<A
NAME="IX-PGTCL-PGEXECUTE-2"
></A
></DIV
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN1006"
></A
><H2
>Synopsis</H2
><PRE
CLASS="SYNOPSIS"
>pg_execute ?<SPAN
CLASS="OPTIONAL"
>-array <VAR
CLASS="PARAMETER"
>arrayVar</VAR
></SPAN
>? ?<SPAN
CLASS="OPTIONAL"
>-oid <VAR
CLASS="PARAMETER"
>oidVar</VAR
></SPAN
>? <VAR
CLASS="PARAMETER"
>conn</VAR
> <VAR
CLASS="PARAMETER"
>commandString</VAR
> ?<SPAN
CLASS="OPTIONAL"
><VAR
CLASS="PARAMETER"
>procedure</VAR
></SPAN
>?</PRE
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN1016"
></A
><H2
>Description</H2
><P
>   <CODE
CLASS="FUNCTION"
>pg_execute</CODE
> submits a command to the
   <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> server.
  </P
><P
>   If the command is not a <TT
CLASS="COMMAND"
>SELECT</TT
> statement, the
   number of rows affected by the command is returned.  If the command
   is an <TT
CLASS="COMMAND"
>INSERT</TT
> statement and a single row is
   inserted, the OID of the inserted row is stored in the variable
   <VAR
CLASS="PARAMETER"
>oidVar</VAR
> if the optional <VAR
CLASS="PARAMETER"
>-oid</VAR
>
   argument is supplied.
  </P
><P
>   If the command is a <TT
CLASS="COMMAND"
>SELECT</TT
> statement, then, for
   each row in the result, the row values are stored in the
   <VAR
CLASS="PARAMETER"
>arrayVar</VAR
> variable, if supplied, using the
   column names as the array indices, else in variables named by the
   column names, and then the optional
   <VAR
CLASS="PARAMETER"
>procedure</VAR
> is executed if supplied.
   (Omitting the <VAR
CLASS="PARAMETER"
>procedure</VAR
> probably makes sense
   only if the query will return a single row.)  The number of rows
   selected is returned.
  </P
><P
>   The <VAR
CLASS="PARAMETER"
>procedure</VAR
> can use the Tcl commands
   <TT
CLASS="LITERAL"
>break</TT
>, <TT
CLASS="LITERAL"
>continue</TT
>, and
   <TT
CLASS="LITERAL"
>return</TT
> with the expected behavior.  Note that if
   the <VAR
CLASS="PARAMETER"
>procedure</VAR
> executes
   <TT
CLASS="LITERAL"
>return</TT
>, then <CODE
CLASS="FUNCTION"
>pg_execute</CODE
>
   does not return the number of affected rows.
  </P
><P
>   <CODE
CLASS="FUNCTION"
>pg_execute</CODE
> is a newer function which provides
   a superset of the features of <CODE
CLASS="FUNCTION"
>pg_select</CODE
> and
   can replace <CODE
CLASS="FUNCTION"
>pg_exec</CODE
> in many cases where access
   to the result handle is not needed.
  </P
><P
>   For server-handled errors, <CODE
CLASS="FUNCTION"
>pg_execute</CODE
> will
   throw a Tcl error and return a two-element list.  The first element
   is an error code, such as <TT
CLASS="LITERAL"
>PGRES_FATAL_ERROR</TT
>, and
   the second element is the server error text.  For more serious
   errors, such as failure to communicate with the server,
   <CODE
CLASS="FUNCTION"
>pg_execute</CODE
> will throw a Tcl error and return
   just the error message text.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN1047"
></A
><H2
>Arguments</H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><VAR
CLASS="OPTION"
>-array <VAR
CLASS="PARAMETER"
>arrayVar</VAR
></VAR
></DT
><DD
><P
>      Specifies the name of an array variable where result rows are
      stored, indexed by the column names.  This is ignored if
      <VAR
CLASS="PARAMETER"
>commandString</VAR
> is not a <TT
CLASS="COMMAND"
>SELECT</TT
>
      statement.
     </P
></DD
><DT
><VAR
CLASS="OPTION"
>-oid <VAR
CLASS="PARAMETER"
>oidVar</VAR
></VAR
></DT
><DD
><P
>      Specifies the name of a variable into which the OID from an
      <TT
CLASS="COMMAND"
>INSERT</TT
> statement will be stored.
     </P
></DD
><DT
><VAR
CLASS="PARAMETER"
>conn</VAR
></DT
><DD
><P
>      The handle of the connection on which to execute the command.
     </P
></DD
><DT
><VAR
CLASS="PARAMETER"
>commandString</VAR
></DT
><DD
><P
>      The SQL command to execute.
     </P
></DD
><DT
><VAR
CLASS="PARAMETER"
>procedure</VAR
></DT
><DD
><P
>      Optional procedure to execute for each result row of a
      <TT
CLASS="COMMAND"
>SELECT</TT
> statement.
     </P
></DD
></DL
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN1081"
></A
><H2
>Return Value</H2
><P
>   The number of rows affected or returned by the command.
  </P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN1084"
></A
><H2
>Examples</H2
><P
>   In the following examples, error checking with
   <TT
CLASS="LITERAL"
>catch</TT
> has been omitted for clarity.
  </P
><P
>   Insert a row and save the OID in <VAR
CLASS="VARNAME"
>result_oid</VAR
>:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_execute -oid result_oid $pgconn "INSERT INTO mytable VALUES (1);"</PRE
><P>
  </P
><P
>   Print the columns <TT
CLASS="LITERAL"
>item</TT
> and <TT
CLASS="LITERAL"
>value</TT
> from each
   row:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_execute -array d $pgconn "SELECT item, value FROM mytable;" {
    puts "Item=$d(item) Value=$d(value)"
}</PRE
><P>
  </P
><P
>   Find the maximum and minimum values and store them in
   <TT
CLASS="LITERAL"
>$s(max)</TT
> and <TT
CLASS="LITERAL"
>$s(min)</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_execute -array s $pgconn "SELECT max(value) AS max, min(value) AS min FROM mytable;"</PRE
><P>
  </P
><P
>   Find the maximum and minimum values and store them in
   <TT
CLASS="LITERAL"
>$max</TT
> and <TT
CLASS="LITERAL"
>$min</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>pg_execute $pgconn "SELECT max(value) AS max, min(value) AS min FROM mytable;"</PRE
><P>
  </P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="pgtcl-pgselect.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="pgtcl-pglisten.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>pg_select</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="libpgtcl-ref.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>pg_listen</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>