PostgreSQL 7.3.4 Documentation
Prev |  | Next

# pg_execute

## Name

pg_execute -- send a query and optionally loop over the results

## Synopsis

    pg_execute [-array arrayVar] [-oid oidVar] dbHandle queryString [queryProcedure]

### 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.

### Outputs

ntuples

  The number of tuples affected or returned by the query.

## 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"

Prev | Home | Next
pg_select | Up | pg_listen