libpgtcl.sgml

关系型数据库 Postgresql 6.5.2

<Chapter Id="pgtcl">
<Title>pgtcl</Title>

<Para>
<literal>pgtcl</literal> is a tcl package for front-end programs 
to interface with <ProductName>Postgres</ProductName>
backends.  It makes most of the functionality of <literal>libpq</literal> available to
tcl scripts.
</Para>

<Para>
This package was originally written by Jolly Chen.
</Para>

<Sect1>
<Title>Commands</Title>

<Para>
<TABLE TOCENTRY="1">
<TITLE><literal>pgtcl</literal> Commands</TITLE>
<TGROUP COLS="2">
<THEAD>
  <ROW>
    <ENTRY>Command</ENTRY>
    <ENTRY>Description</ENTRY>
  </ROW>
</THEAD>
<TBODY>
  <ROW>
    <ENTRY>pg_connect</ENTRY>
    <ENTRY>opens a connection to the backend server</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_disconnect</ENTRY>
    <ENTRY>closes a connection</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_conndefaults</ENTRY>
    <ENTRY>get connection options and their defaults</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_exec</ENTRY>
    <ENTRY>send a query to the backend</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_result</ENTRY>
    <ENTRY>manipulate the results of a query</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_select</ENTRY>
    <ENTRY>loop over the result of a SELECT statement</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_listen</ENTRY>
    <ENTRY>establish a callback for NOTIFY messages</ENTRY>
  </ROW>

  <ROW>
    <ENTRY>pg_lo_creat</ENTRY>
    <ENTRY>create a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_open</ENTRY>
    <ENTRY>open a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_close</ENTRY>
    <ENTRY>close a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_read</ENTRY>
    <ENTRY>read a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_write</ENTRY>
    <ENTRY>write a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_lseek</ENTRY>
    <ENTRY>seek to a position in a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_tell</ENTRY>
    <ENTRY>return the current seek position of a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_unlink</ENTRY>
    <ENTRY>delete a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_import</ENTRY>
    <ENTRY>import a Unix file into a large object</ENTRY>
  </ROW>
  <ROW>
    <ENTRY>pg_lo_export</ENTRY>
    <ENTRY>export a large object into a Unix file</ENTRY>
  </ROW>
</TBODY>
</TGROUP>
</TABLE>
</Para>

<Para>
These commands are described further on subsequent pages.
</Para>

<Para>
The pg_lo* routines are interfaces to the Large Object features of
<ProductName>Postgres</ProductName>.
The functions are designed to mimic the analogous file system functions in
the standard Unix file system interface.
The pg_lo* routines should be used within a BEGIN/END transaction
block because the file descriptor returned by pg_lo_open is only valid for
the current transaction.  pg_lo_import and pg_lo_export MUST be used
in a BEGIN/END transaction block.
</Para>

</Sect1>

<Sect1>
<Title>Examples</Title>

<Para>
Here's a small example of how to use the routines:

<ProgramListing>
# getDBs :
#   get the names of all the databases at a given host and port number
#   with the defaults being the localhost and port 5432
#   return them in alphabetical order
proc getDBs { {host "localhost"} {port "5432"} } {
    # datnames is the list to be result
    set conn [pg_connect template1 -host $host -port $port]
    set res [pg_exec $conn "SELECT datname FROM pg_database ORDER BY datname"]
    set ntups [pg_result $res -numTuples]
    for {set i 0} {$i < $ntups} {incr i} {
	lappend datnames [pg_result $res -getTuple $i]
    }
    pg_result $res -clear
    pg_disconnect $conn
    return $datnames
}
</ProgramListing>

</Para>
</Sect1>

<Sect1>
<Title>pgtcl Command Reference Information</Title>

<REFENTRY ID="PGTCL-PGCONNECT">
<REFMETA>
<REFENTRYTITLE>pg_connect</REFENTRYTITLE>
<REFMISCINFO>PGTCL - Connection Management</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>pg_connect
</REFNAME>
<REFPURPOSE>opens a connection to the backend server
</REFPURPOSE>
<INDEXTERM ID="IX-PGTCL-PGCONNECT-1"><PRIMARY>pgtcl</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-PGTCL-PGCONNECT-2"><PRIMARY>pg_connect</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
pg_connect -conninfo <REPLACEABLE CLASS="PARAMETER">connectOptions</REPLACEABLE>
pg_connect <REPLACEABLE CLASS="PARAMETER">dbName</REPLACEABLE> <OPTIONAL>-host <REPLACEABLE CLASS="PARAMETER">hostName</REPLACEABLE></OPTIONAL>
  <OPTIONAL>-port <REPLACEABLE
  CLASS="PARAMETER">portNumber</REPLACEABLE></OPTIONAL> <OPTIONAL>-tty <REPLACEABLE
  CLASS="PARAMETER">pqtty</REPLACEABLE></OPTIONAL>
  <OPTIONAL>-options <REPLACEABLE
  CLASS="PARAMETER">optionalBackendArgs</REPLACEABLE></OPTIONAL>
</SYNOPSIS>

<REFSECT2 ID="R2-PGTCL-PGCONNECT-1">
<REFSECT2INFO>
<DATE>1998-10-07</DATE>
</REFSECT2INFO>
<TITLE>Inputs (new style)
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">connectOptions</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>A string of connection options, each written in the form keyword = value.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-PGTCL-PGCONNECT-2">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs (old style)
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">dbName</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>Specifies a valid database name.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
  <OPTIONAL>-host <REPLACEABLE CLASS="PARAMETER">hostName</REPLACEABLE></OPTIONAL>
</TERM>
<LISTITEM>
<PARA>Specifies the domain name of the backend server for <REPLACEABLE CLASS="PARAMETER">dbName</REPLACEABLE>.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
  <OPTIONAL>-port <REPLACEABLE CLASS="PARAMETER">portNumber</REPLACEABLE></OPTIONAL>
</TERM>
<LISTITEM>
<PARA>Specifies the IP port number of the backend server for <REPLACEABLE CLASS="PARAMETER">dbName</REPLACEABLE>.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
  <OPTIONAL>-tty <REPLACEABLE CLASS="PARAMETER">pqtty</REPLACEABLE></OPTIONAL>
</TERM>
<LISTITEM>
<PARA>Specifies file or tty for optional debug output from backend.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
  <OPTIONAL>-options <REPLACEABLE CLASS="PARAMETER">optionalBackendArgs</REPLACEABLE></OPTIONAL>
</TERM>
<LISTITEM>
<PARA>Specifies options for the backend server for <REPLACEABLE CLASS="PARAMETER">dbName</REPLACEABLE>.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-PGTCL-PGCONNECT-3">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">dbHandle</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
If successful, a handle for a database connection is returned.
Handles start with the prefix "pgsql".
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>

<!-- ********************************************************** -->

<REFSECT1 ID="R1-PGTCL-PGCONNECT-1">
<REFSECT1INFO>
<DATE>1997-12-24</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>
<PARA><FUNCTION>pg_connect</FUNCTION> opens a connection to the
<ProductName>Postgres</ProductName> backend.
</Para>

<para>
Two syntaxes are available.  In the older one, each possible option
has a separate option switch in the pg_connect statement.  In the
newer form, a single option string is supplied that can contain
multiple option values.  See <FUNCTION>pg_conndefaults</FUNCTION>
for info about the available options in the newer syntax.
</PARA>
</REFSECT1>
<REFSECT1 ID="R1-PGTCL-PGCONNECT-2">
<TITLE>Usage
</TITLE>
<PARA>XXX thomas 1997-12-24
</PARA>
</REFSECT1>
</REFENTRY>

<REFENTRY ID="PGTCL-PGDISCONNECT">
<REFMETA>
<REFENTRYTITLE>pg_disconnect</REFENTRYTITLE>
<REFMISCINFO>PGTCL - Connection Management</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>pg_disconnect
</REFNAME>
<REFPURPOSE>closes a connection to the backend server
</REFPURPOSE>
<INDEXTERM ID="IX-PGTCL-PGDISCONNECT-1"><PRIMARY>pgtcl</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-PGTCL-PGDISCONNECT-2"><PRIMARY>pg_connect</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
pg_disconnect <REPLACEABLE CLASS="PARAMETER">dbHandle</REPLACEABLE>
</SYNOPSIS>

<REFSECT2 ID="R2-PGTCL-PGDISCONNECT-1">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">dbHandle</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>Specifies a valid database handle.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-PGTCL-PGDISCONNECT-2">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  None
</TERM>
<LISTITEM>
<PARA>
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-PGTCL-PGDISCONNECT-1">
<REFSECT1INFO>
<DATE>1997-12-24</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>
<PARA><FUNCTION>pg_disconnect</FUNCTION> closes a connection to the <ProductName>Postgres</ProductName> backend.
</PARA>
</REFSECT1>

</REFENTRY>




<REFENTRY ID="PGTCL-PGCONNDEFAULTS">
<REFMETA>
<REFENTRYTITLE>pg_conndefaults</REFENTRYTITLE>
<REFMISCINFO>PGTCL - Connection Management</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>pg_conndefaults
</REFNAME>
<REFPURPOSE>obtain information about default connection parameters
</REFPURPOSE>
<INDEXTERM ID="IX-PGTCL-PGCONNDEFAULTS-1"><PRIMARY>pgtcl</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-PGTCL-PGCONNDEFAULTS-2"><PRIMARY>pg_conndefaults</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1998-10-07</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
pg_conndefaults
</SYNOPSIS>

<REFSECT2 ID="R2-PGTCL-PGCONNDEFAULTS-1">
<REFSECT2INFO>
<DATE>1998-10-07</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<PARA>
None.
</PARA>
</REFSECT2>

<REFSECT2 ID="R2-PGTCL-PGCONNDEFAULTS-2">
<REFSECT2INFO>
<DATE>1998-10-07</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">option list</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
The result is a list describing the possible connection options and their
current default values.
Each entry in the list is a sublist of the format:
</Para>
<para>
	 {optname label dispchar dispsize value}
</Para>
<Para>
where the optname is usable as an option in
<FUNCTION>pg_connect -conninfo</FUNCTION>.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2>
</REFSYNOPSISDIV>

<REFSECT1 ID="R1-PGTCL-PGCONNDEFAULTS-1">
<REFSECT1INFO>
<DATE>1998-10-07</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>

<para>
<FUNCTION>pg_conndefaults</FUNCTION> returns info about the connection
options available in <FUNCTION>pg_connect -conninfo</FUNCTION> and the
current default value for each option.
</PARA>
</REFSECT1>
<REFSECT1 ID="R1-PGTCL-PGCONNDEFAULTS-2">
<TITLE>Usage
</TITLE>
<PARA>pg_conndefaults
</PARA>
</REFSECT1>
</REFENTRY>

<REFENTRY ID="PGTCL-PGEXEC">
<REFMETA>
<REFENTRYTITLE>pg_exec</REFENTRYTITLE>
<REFMISCINFO>PGTCL - Query Processing</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>pg_exec
</REFNAME>
<REFPURPOSE>
send a query string to the backend 
</REFPURPOSE>
<INDEXTERM ID="IX-PGTCL-PGEXEC-1"><PRIMARY>pgtcl</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-PGTCL-PGEXEC-2"><PRIMARY>pg_connect</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
pg_exec <REPLACEABLE CLASS="PARAMETER">dbHandle</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">queryString</REPLACEABLE>
</SYNOPSIS>

<!-- ********************************************************** -->

<REFSECT2 ID="R2-PGTCL-PGEXEC-1">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<VARIABLELIST>
<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>
</VARIABLELIST>
</REFSECT2>

<REFSECT2 ID="R2-PGTCL-PGEXEC-2">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Outputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">resultHandle</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
A Tcl error will be returned if Pgtcl was unable to obtain a backend
response.  Otherwise, a query result object is created and a handle for
it is returned.  This handle can be passed to <FUNCTION>pg_result</FUNCTION>
to obtain the results of the query.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>
</REFSECT2></REFSYNOPSISDIV>

<REFSECT1 ID="R1-PGTCL-PGEXEC-1">
<REFSECT1INFO>
<DATE>1997-12-24</DATE>
</REFSECT1INFO>
<TITLE>Description
</TITLE>
<PARA>
<FUNCTION>pg_exec</FUNCTION> submits a query to the <ProductName>Postgres</ProductName> backend and returns a result.

Query result handles start with the connection handle and add a period
and a result number.
</Para>

<PARA>
Note that lack of a Tcl error is not proof that the query succeeded!
An error message returned by the backend will be processed
as a query result with failure status, not by generating a Tcl error
in pg_exec.
</PARA>
</REFSECT1>
</refentry>

<REFENTRY ID="PGTCL-PGRESULT">
<REFMETA>
<REFENTRYTITLE>pg_result</REFENTRYTITLE>
<REFMISCINFO>PGTCL - Query Processing</REFMISCINFO>
</REFMETA>
<REFNAMEDIV>
<REFNAME>pg_result
</REFNAME>
<REFPURPOSE>
get information about a query result
</REFPURPOSE>
<INDEXTERM ID="IX-PGTCL-PGRESULT-1"><PRIMARY>pgtcl</PRIMARY><SECONDARY>connecting</SECONDARY></INDEXTERM>
<INDEXTERM ID="IX-PGTCL-PGRESULT-2"><PRIMARY>pg_connect</PRIMARY></INDEXTERM>
</REFNAMEDIV>
<REFSYNOPSISDIV>
<REFSYNOPSISDIVINFO>
<DATE>1997-12-24</DATE>
</REFSYNOPSISDIVINFO>
<SYNOPSIS>
pg_result <REPLACEABLE CLASS="PARAMETER">resultHandle</REPLACEABLE> <REPLACEABLE CLASS="PARAMETER">resultOption</REPLACEABLE>
</SYNOPSIS>
<REFSECT2 ID="R2-PGTCL-PGRESULT-1">
<REFSECT2INFO>
<DATE>1997-12-24</DATE>
</REFSECT2INFO>
<TITLE>Inputs
</TITLE>
<VARIABLELIST>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">resultHandle</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
  The handle for a query result.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
  <REPLACEABLE CLASS="PARAMETER">resultOption</REPLACEABLE>
</TERM>
<LISTITEM>
<PARA>
Specifies one of several possible options.
</PARA>
</LISTITEM>
</VARLISTENTRY>
</VARIABLELIST>

<REFSECT3>
<TITLE>Options</TITLE>

<VARIABLELIST>
<VARLISTENTRY>
<TERM>
-status  
</TERM>
<LISTITEM>
<PARA>
the status of the result.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
-error
</TERM>
<LISTITEM>
<PARA>
the error message, if the status indicates error; otherwise an empty string.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
-conn
</TERM>
<LISTITEM>
<PARA>
the connection that produced the result.
</PARA>
</LISTITEM>
</VARLISTENTRY>
<VARLISTENTRY>
<TERM>
-oid
</TERM>
<LISTITEM>
<PARA>
if the command was an INSERT, the OID of the 
inserted tuple; otherwise an empty string.
</PARA>
</LISTITEM>
</VARLISTENTRY>