Inputs +

Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id A0CCA475ED5 for ; Wed, 13 Mar 2002 21:06:11 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g2E1b2h67680 for pgsql-docs@postgresql.org; Wed, 13 Mar 2002 19:37:02 -0600 (CST) (envelope-from news) From: ljb X-Newsgroups: comp.databases.postgresql.docs Subject: For review: documentation for libpgtcl pg_execute Date: Thu, 14 Mar 2002 01:37:02 +0000 (UTC) Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 227 Message-ID: X-Complaints-To: news@hub.org X-No-Archive: yes User-Agent: slrn/0.9.7.3 (Linux) To: pgsql-docs@postgresql.org X-Archive-Number: 200203/33 X-Sequence-Number: 1314 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. *** libpgtcl.sgml.orig Sun Jan 20 17:19:56 2002 --- libpgtcl.sgml Wed Mar 13 20:10:35 2002 *************** *** 65,70 **** --- 65,73 ---- loop over the result of a SELECT statement + pg_execute + send a query and optionally loop over the results + pg_listen establish a callback for NOTIFY messages *************** *** 986,991 **** --- 989,1190 ---- pg_select $pgconn "SELECT * FROM table" array { puts [format "%5d %s" $array(control) $array(name)] } + + + + + + + + + + + pg_execute + PGTCL - Query Processing + + + pg_execute + + + send a query and optionally loop over the results + + pgtclquery + pg_execute + + + + 2002-03-06 + + + pg_execute -array arrayVar -oid oidVar dbHandle queryString queryProcedure + + + + + 2002-03-06 + + Inputs + + + + + -array arrayVar + + + 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. + + + + + + -oid oidVar + + + Specifies the name of a variable into which the OID from an INSERT + statement will be stored. + + + + + + dbHandle + + + Specifies a valid database handle. + + + + + + queryString + + + Specifies a valid SQL query. + + + + + + queryProcedure + + + Optional command to execute for each result tuple of a SELECT statement. + + + + + + + + + + 2002-03-06 + + Outputs + + + + + ntuples + + + + The number of tuples affected or returned by the query. + + + + + + + + + 2002-03-06 + + Description + + + pg_execute submits a query to the PostgreSQL backend. + + + 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 -oid + argument is supplied. + + + 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 + arrayVar variable, + if supplied, using the field names as the array indexes, else in variables + named by the field names, and then the optional + queryProcedure is executed if supplied. + (Omitting the queryProcedure probably makes sense + only if the query will return a single tuple.) + The number of tuples selected is returned. + + + The queryProcedure can use the Tcl + break, continue, and + return commands, with the expected behavior. + Note that if the queryProcedure executes + return, pg_execute does + not return ntuples. + + + pg_execute is a newer function which provides a + superset of the features of pg_select, and can + replace pg_exec in many cases where access to + the result handle is not needed. + + + For backend-handled errors, pg_execute will + throw a Tcl error and return two element list. The first element + is an error code such as PGRES_FATAL_ERROR, and + the second element is the backend error text. For more serious + errors, such as failure to communicate with the backend, + pg_execute will throw a Tcl error and return + just the error message text. + + + + + + Usage + + + In the following examples, error checking with catch + has been omitted for clarity. + + + Insert a row and save the OID in result_oid: + + pg_execute -oid result_oid $pgconn "insert into mytable values (1)" + + + + Print the item and value fields from each row: + + pg_execute -array d $pgconn "select item, value from mytable" { + puts "Item=$d(item) Value=$d(value)" + } + + + + Find the maximum and minimum values and store them in $s(max) and $s(min): + + pg_execute -array s $pgconn "select max(value) as max,\ + min(value) as min from mytable" + + + + Find the maximum and minimum values and store them in $max and $min: + + pg_execute $pgconn "select max(value) as max, min(value) as min from mytable"