From: ljb <lbayuk@mindspring.com>
To: pgsql-docs@postgresql.org
Subject: For review: documentation for libpgtcl pg_execute
Date: Thu, 14 Mar 2002 01:37:02 +0000 (UTC)
Message-ID: <a6ours$1vp2$2@jupiter.hub.org> (raw)
This is a proposed patch to doc/src/sgml/libpgtcl.sgml which documents the
libpgtcl "pg_execute" command. This was mentioned on pgsql-interfaces on
Mar 3. I am posting it here in the hope that someone will check to see if
it makes sense and is correct SGML-wise. I did run it through jade, but this
is my first try at this sort of thing. Corrections would be appreciated.
Thanks.
inline patch 1: libpgtcl.sgml (+199/-0 lines) download inline-1.patch*** libpgtcl.sgml.orig Sun Jan 20 17:19:56 2002
--- libpgtcl.sgml Wed Mar 13 20:10:35 2002
***************
*** 65,70 ****
--- 65,73 ----
<ENTRY>loop over the result of a SELECT statement</ENTRY>
</ROW>
<ROW>
+ <ENTRY><function>pg_execute</function></ENTRY>+ <ENTRY>send a query and optionally loop over the results</ENTRY>+ <ROW>
<ENTRY><function>pg_listen</function></ENTRY>
<ENTRY>establish a callback for NOTIFY messages</ENTRY>
</ROW>
***************
*** 986,991 ****
--- 989,1190 ----
pg_select $pgconn "SELECT * FROM table" array {
puts [format "%5d %s" $array(control) $array(name)]
}
+ </ProgramListing>+ </PARA>+ </REFSECT1>+ + </REFENTRY>+ + <!-- ********************************************************** -->+ + <REFENTRY ID="PGTCL-PGEXECUTE">+ <REFMETA>+ <REFENTRYTITLE>pg_execute</REFENTRYTITLE>+ <REFMISCINFO>PGTCL - Query Processing</REFMISCINFO>+ </REFMETA>+ <REFNAMEDIV>+ <REFNAME>pg_execute+ </REFNAME>+ <REFPURPOSE>+ send a query and optionally loop over the results+ </REFPURPOSE>+ <INDEXTERM ID="IX-PGTCL-PGEXECUTE-1"><PRIMARY>pgtcl</PRIMARY><SECONDARY>query</SECONDARY></INDEXTERM>+ <INDEXTERM ID="IX-PGTCL-PGEXECUTE-2"><PRIMARY>pg_execute</PRIMARY></INDEXTERM>+ </REFNAMEDIV>+ <REFSYNOPSISDIV>+ <REFSYNOPSISDIVINFO>+ <DATE>2002-03-06</DATE>+ </REFSYNOPSISDIVINFO>+ <SYNOPSIS>+ pg_execute <OPTIONAL>-array <REPLACEABLE CLASS="PARAMETER">arrayVar</REPLACEABLE></OPTIONAL> <OPTIONAL>-oid <REPLACEABLE CLASS="PARAMETER">oidVar</REPLACEABLE></OPTIONAL> <REPLACEABLE CLASS="PARAMETER">dbHandle</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">queryString</REPLACEABLE> <OPTIONAL><REPLACEABLE CLASS="PARAMETER">queryProcedure</REPLACEABLE></OPTIONAL>+ </SYNOPSIS>+ + <REFSECT2 ID="R2-PGTCL-PGEXECUTE-1">+ <REFSECT2INFO>+ <DATE>2002-03-06</DATE>+ </REFSECT2INFO>+ <TITLE>Inputs+ </TITLE>+ <VARIABLELIST>+ <VARLISTENTRY>+ <TERM>+ <OPTIONAL>-array <REPLACEABLE CLASS="PARAMETER">arrayVar</REPLACEABLE></OPTIONAL>+ </TERM>+ <LISTITEM>+ <PARA>Specifies the name of an array variable where result tuples are stored,+ indexed by the field names.+ This is ignored if queryString is not a SELECT statement. For SELECT+ statements, if this option is not used, result tuples values are stored+ in individual variables named according to the field names in the result.+ </PARA>+ </LISTITEM>+ </VARLISTENTRY>+ <VARLISTENTRY>+ <TERM>+ <OPTIONAL>-oid <REPLACEABLE CLASS="PARAMETER">oidVar</REPLACEABLE></OPTIONAL>+ </TERM>+ <LISTITEM>+ <PARA>Specifies the name of a variable into which the OID from an INSERT+ statement will be stored.+ </PARA>+ </LISTITEM>+ </VARLISTENTRY>+ <VARLISTENTRY>+ <TERM>+ <REPLACEABLE CLASS="PARAMETER">dbHandle</REPLACEABLE>+ </TERM>+ <LISTITEM>+ <PARA>Specifies a valid database handle.+ </PARA>+ </LISTITEM>+ </VARLISTENTRY>+ <VARLISTENTRY>+ <TERM>+ <REPLACEABLE CLASS="PARAMETER">queryString</REPLACEABLE>+ </TERM>+ <LISTITEM>+ <PARA>Specifies a valid SQL query.+ </PARA>+ </LISTITEM>+ </VARLISTENTRY>+ <VARLISTENTRY>+ <TERM>+ <OPTIONAL><REPLACEABLE CLASS="PARAMETER">queryProcedure</REPLACEABLE></OPTIONAL>+ </TERM>+ <LISTITEM>+ <PARA>Optional command to execute for each result tuple of a SELECT statement.+ </PARA>+ </LISTITEM>+ </VARLISTENTRY>+ </VARIABLELIST>+ + </REFSECT2>+ + <REFSECT2 ID="R2-PGTCL-PGEXECUTE-2">+ <REFSECT2INFO>+ <DATE>2002-03-06</DATE>+ </REFSECT2INFO>+ <TITLE>Outputs+ </TITLE>+ <VARIABLELIST>+ <VARLISTENTRY>+ <TERM>+ <REPLACEABLE CLASS="PARAMETER">ntuples</REPLACEABLE>+ </TERM>+ <LISTITEM>+ <PARA>+ The number of tuples affected or returned by the query.+ </PARA>+ </LISTITEM>+ </VARLISTENTRY>+ </VARIABLELIST>+ </REFSECT2></REFSYNOPSISDIV>+ + <REFSECT1 ID="R1-PGTCL-PGEXECUTE-1">+ <REFSECT1INFO>+ <DATE>2002-03-06</DATE>+ </REFSECT1INFO>+ <TITLE>Description+ </TITLE>+ <PARA>+ <FUNCTION>pg_execute</FUNCTION> submits a query to the PostgreSQL backend.+ </PARA>+ <PARA>+ If the query is not a SELECT statement, the query is executed and the+ number of tuples affected by the query is returned. If the query is an+ INSERT and a single tuple is inserted, the OID of the inserted tuple is+ stored in the oidVar variable if the optional <PARAMETER>-oid</PARAMETER>+ argument is supplied.+ </PARA>+ <PARA>+ If the query is a SELECT statement, the query is executed. For each tuple+ in the result, the tuple field values are stored in the+ <PARAMETER>arrayVar</PARAMETER> variable,+ if supplied, using the field names as the array indexes, else in variables+ named by the field names, and then the optional+ <PARAMETER>queryProcedure</PARAMETER> is executed if supplied.+ (Omitting the <PARAMETER>queryProcedure</PARAMETER> probably makes sense+ only if the query will return a single tuple.)+ The number of tuples selected is returned.+ </PARA>+ <PARA>+ The <PARAMETER>queryProcedure</PARAMETER> can use the Tcl+ <LITERAL>break</LITERAL>, <LITERAL>continue</LITERAL>, and+ <LITERAL>return</LITERAL> commands, with the expected behavior.+ Note that if the <PARAMETER>queryProcedure</PARAMETER> executes+ <LITERAL>return</LITERAL>, <FUNCTION>pg_execute</FUNCTION> does+ not return <PARAMETER>ntuples</PARAMETER>.+ </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 backend-handled errors, <FUNCTION>pg_execute</FUNCTION> will+ throw a Tcl error and return two element list. The first element+ is an error code such as <LITERAL>PGRES_FATAL_ERROR</LITERAL>, and+ the second element is the backend error text. For more serious+ errors, such as failure to communicate with the backend,+ <FUNCTION>pg_execute</FUNCTION> will throw a Tcl error and return+ just the error message text.+ </PARA>+ + </REFSECT1>+ + <REFSECT1 ID="R1-PGTCL-PGEXECUTE-2">+ <TITLE>Usage+ </TITLE>+ <PARA>+ In the following examples, error checking with <LITERAL>catch</LITERAL>+ has been omitted for clarity.+ </PARA>+ <PARA>+ Insert a row and save the OID in result_oid:+ <ProgramListing>+ pg_execute -oid result_oid $pgconn "insert into mytable values (1)"+ </ProgramListing>+ </PARA>+ <PARA>+ Print the item and value fields from each row:+ <ProgramListing>+ pg_execute -array d $pgconn "select item, value from mytable" {+ puts "Item=$d(item) Value=$d(value)"+ }+ </ProgramListing>+ </PARA>+ <PARA>+ Find the maximum and minimum values and store them in $s(max) and $s(min):+ <ProgramListing>+ pg_execute -array s $pgconn "select max(value) as max,\+ min(value) as min from mytable"+ </ProgramListing>+ </PARA>+ <PARA>+ Find the maximum and minimum values and store them in $max and $min:+ <ProgramListing>+ pg_execute $pgconn "select max(value) as max, min(value) as min from mytable"
</ProgramListing>
</PARA>
</REFSECT1>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: lbayuk@mindspring.com
Subject: Re: For review: documentation for libpgtcl pg_execute
In-Reply-To: <a6ours$1vp2$2@jupiter.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox