<!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
REL="HOME"
TITLE="Pgtcl Reference Manual"
HREF="index.html"><LINK
REL="UP"
TITLE="Query Execution Commands"
HREF="pgtcl-ref-query.html"><LINK
REL="PREVIOUS"
TITLE="pg_exec"
HREF="pg-exec.html"><LINK
REL="NEXT"
TITLE="pg_select"
HREF="pg-select.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
NAME="creation"
CONTENT="2004-11-09T00:53:06"></HEAD
><BODY
CLASS="REFENTRY"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>Pgtcl Reference Manual: The PostgreSQL Tcl Interface</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="pg-exec.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="pg-select.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="PG-EXECUTE"
></A
>pg_execute</H1
><DIV
CLASS="REFNAMEDIV"
><A
NAME="AEN933"
></A
><H2
>Name</H2
>pg_execute -- send a command to the server and optionally loop over the results</DIV
><DIV
CLASS="REFSYNOPSISDIV"
><A
NAME="AEN936"
></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="AEN946"
></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="REPLACEABLE"
>oidVar</VAR
> if the optional <VAR
CLASS="OPTION"
>-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="REPLACEABLE"
>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="REPLACEABLE"
>procedure</VAR
> is executed if
supplied. (Omitting the <VAR
CLASS="REPLACEABLE"
>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="REPLACEABLE"
>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="REPLACEABLE"
>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 command which provides
a superset of the features of <A
HREF="pg-select.html"
>pg_select</A
> and
can replace <A
HREF="pg-exec.html"
>pg_exec</A
> in many cases where access
to the result handle is not needed.
</P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN973"
></A
><H2
>Arguments</H2
><P
></P
><DIV
CLASS="VARIABLELIST"
><DL
><DT
><VAR
CLASS="OPTION"
>-array <VAR
CLASS="REPLACEABLE"
>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="REPLACEABLE"
>commandString</VAR
> is not a
<TT
CLASS="COMMAND"
>SELECT</TT
> statement.
</P
></DD
><DT
><VAR
CLASS="OPTION"
>-oid <VAR
CLASS="REPLACEABLE"
>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="REPLACEABLE"
>conn</VAR
></DT
><DD
><P
> The handle of the connection on which to execute the command.
</P
></DD
><DT
><VAR
CLASS="REPLACEABLE"
>commandString</VAR
></DT
><DD
><P
> The SQL command to execute.
</P
></DD
><DT
><VAR
CLASS="REPLACEABLE"
>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="AEN1007"
></A
><H2
>Return Value</H2
><P
> The number of rows affected or returned by the command.
</P
><P
> A Tcl error will be thrown if an error occurs communicating with
the database, or if there is an error processing the query. Note
that this differs from <A
HREF="pg-exec.html"
>pg_exec</A
>, which will not
throw a Tcl error for a query which generates a database error.
A Tcl error will also be thrown if a procedure body is supplied
and its execution results in a Tcl error.
</P
><P
> For database server handled errors, <CODE
CLASS="FUNCTION"
>pg_execute</CODE
> will
throw a Tcl error and the error message will be 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 database server error text.
Unfortunately, other errors also result in a Tcl error being thrown
but the error message is not a list - just the message text, so
the list form of the error message cannot be assumed.
</P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN1015"
></A
><H2
>Notes</H2
><P
> <CODE
CLASS="FUNCTION"
>pg_execute</CODE
> cannot be used to start a
<TT
CLASS="COMMAND"
>COPY</TT
> because it doesn't return a result handle.
You must use <A
HREF="pg-exec.html"
>pg_exec</A
> to start a
<TT
CLASS="COMMAND"
>COPY</TT
>.
</P
><P
> This command uses or emulates the <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
>
<SPAN
CLASS="APPLICATION"
>libpq</SPAN
>
function <CODE
CLASS="FUNCTION"
>PQexec</CODE
>.
</P
></DIV
><DIV
CLASS="REFSECT1"
><A
NAME="AEN1026"
></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 $conn "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 $conn "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 $conn "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 $conn "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="pg-exec.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="pg-select.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>pg_exec</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="pgtcl-ref-query.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>pg_select</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
