LIBPQ(INTRO)                PostgreSQL               LIBPQ(INTRO)

DESCRIPTION
       Current  documentation  for this topic is available in the
       new Programmer's Guide chapter on libpq.  This man page is
       obsolete, though in sync with the Programmer's Guide as of
       1998/08/15.

       Libpq is the programmer's interface to Postgres.  Libpq is
       a  set of library routines which allows client programs to
       pass queries to the Postgres backend server and to receive
       the results of these queries.

       This  version  of the documentation describes the C inter-
       face library.  Three short programs are  included  at  the
       end of this section to show how to write programs that use
       Libpq.

       There are several examples of Libpq  applications  in  the
       following directories:
       ../src/test/regress
       ../src/test/examples
       ../src/bin/psql

       Frontend  programs which use Libpq must include the header
       file libpq-fe.h and must link with the libpq library.

Control and Initialization
       The following environment variables can be used to set  up
       default  environment  values to avoid hard-coding database
       names into an application program:

       o PGHOST sets the default server name.  If it is set to  a
       non-zero-length  string, it causes TCP/IP communication to
       be used, rather than the default local Unix  domain  sock-
       ets.

       o PGUSER sets the username used to connect to the database
       and for authentication.

       o PGOPTIONS sets additional runtime options for the  Post-
       gres backend.

       o PGPORT sets the default port or local Unix domain socket
       file extension for communicating with the  Postgres  back-
       end.

       o  PGTTY  sets the file or tty on which debugging messages
       from the backend server are displayed.

       o PGDATABASE sets the default Postgres database name.

       o PGREALM sets the Kerberos realm to use with Postgres, if
       it  is different from the local realm.  If PGREALM is set,
       Postgres applications  will  attempt  authentication  with
       servers  for  this  realm and use separate ticket files to
       avoid conflicts with local ticket files.  This environment
       variable  is  only  used  if  Kerberos  authentication  is
       enabled.

       The following environment variables can be used to specify
       user-level default behavior for every Postgres session:

       o  PGDATESTYLE  sets the default style of date/time repre-
       sentation.

       o PGTZ sets the default time zone.

       The following environment variables can be used to specify
       default internal behavior for every Postgres session:

       o  PGGEQO sets the default mode for the genetic optimizer.

       o PGRPLANS sets the  default  mode  to  allow  or  disable
       right-sided plans in the optimizer.

       o  PGCOSTHEAP  sets the default cost for heap searches for
       the optimizer.

       o PGCOSTINDEX sets the default cost for  indexed  searches
       for  the optimizer.  o PGQUERY_LIMIT sets the maximum num-
       ber of rows returned by a query.

       See the set(l) man page for information on  the  arguments
       for these environment variables.

Database Connection Functions
       The  following routines deal with making a connection to a
       backend from a C program.

       PQsetdb
       PQsetdbLogin

              Makes a new connection to a  backend.   PQsetdb  is
              the  method usually used to connect to the database
              when  username/password   authentication   is   not
              needed.
              PGconn *PQsetdb(char *pghost,
                              char *pgport,
                              char *pgoptions,
                              char *pgtty,
                              char *dbName);

              PQsetdbLogin  is  the method used to connect to the
              database when username/password  authentication  is
              needed.
              PGconn *PQsetdbLogin(char *pghost,
                                   char *pgport,
                                   char *pgoptions,
                                   char *pgtty,
                                   char *dbName,
                                   char *login,
                                   char *pwd);

              If  any  argument  is  NULL, then the corresponding
              environment variable is checked.  If  the  environ-
              ment  variable  is  also  not  set,  then hardwired
              defaults are used.

              PQsetdb and  PQsetdbLogin  always  return  a  valid
              PGconn  pointer.   The PQstatus (see below) command
              should be called to ensure that  a  connection  was
              properly  made before queries are sent via the con-
              nection.  Libpq programmers should  be  careful  to
              maintain  the PGconn abstraction.  Use the accessor
              functions below to get at the contents  of  PGconn.
              Avoid directly referencing the fields of the PGconn
              structure as they are  subject  to  change  in  the
              future.

              PQdb returns the database name of the connection.
              char *PQdb(PGconn *conn)

              PQhost returns the host name of the connection.
              char *PQhost(PGconn *conn)

              PQoptions returns the pgoptions used in the connec-
              tion.
              char *PQoptions(PGconn *conn)

              PQport returns the pgport of the connection.
              char *PQport(PGconn *conn)

              PQtty returns the pgtty of the connection.
              char *PQtty(PGconn *conn)

              PQstatus Returns the status of the connection.  The
              status can be CONNECTION_OK or CONNECTION_BAD.
              ConnStatusType *PQstatus(PGconn *conn)

              PQerrorMessage returns the error message associated
              with the connection
              char *PQerrorMessage(PGconn* conn);

       PQfinish

              Close the connection to the  backend.   Also  frees
              memory  used  by  the PGconn structure.  The PGconn
              pointer should not be used after PQfinish has  been
              called.
              void PQfinish(PGconn *conn)

       PQreset

              Reset  the  communication  port  with  the backend.
              This function will close the IPC socket  connection
              to  the  backend  and  attempt to reestablish a new
              connection to the same backend.
              void PQreset(PGconn *conn)

Query Execution Functions
       PQexec

              Submit a query to Postgres.   Returns  a   PGresult
              pointer  or  possibly a NULL pointer.  If a NULL is
              returned,   it   should   be   treated    like    a
              PGRES_FATAL_ERROR result: use PQerrorMessage to get
              more information about the error.
              PGresult *PQexec(PGconn *conn,
                               const char *query);
              The  PGresult  structure  encapsulates  the   query
              result  returned by the backend.  Libpq programmers
              should be careful to maintain the PGresult abstrac-
              tion. Use the accessor functions described below to
              retrieve the results of the query.  Avoid  directly
              referencing the fields of the PGresult structure as
              they are subject to change in the future.

       PQresultStatus

              Returns the result status of the query.   PQresult-
              Status can return one of the following values:
              PGRES_EMPTY_QUERY,
              PGRES_COMMAND_OK,  /* the query was a command returning no data */
              PGRES_TUPLES_OK,  /* the query successfully returned tuples */
              PGRES_COPY_OUT,
              PGRES_COPY_IN,
              PGRES_BAD_RESPONSE, /* an unexpected response was received */
              PGRES_NONFATAL_ERROR,
              PGRES_FATAL_ERROR

              If   the result status is PGRES_TUPLES_OK, then the
              routines described below can be  used  to  retrieve
              the  tuples  returned  by  the  query.  Note that a
              SELECT that happens to retrieve zero  tuples  still
              shows  PGRES_TUPLES_OK.   PGRES_COMMAND_OK  is  for
              commands that can never return tuples.

       PQresStatus

              Converts the enumerated type returned by  PQresult-
              Status into a string constant describing the status
              code.
              const char *PQresStatus(ExecStatusType status);

              Older code  may  perform  this  same  operation  by
              direct  access  to  a  constant string array inside
              libpq,
              extern const char * const pgresStatus[];

              However, using the function is recommended instead,
              since it is more portable and will not fail on out-
              of-range values.

       PQresultErrorMessage

              returns  the  error  message  associated  with  the
              query, or an empty string if there was no error.
              const char *PQresultErrorMessage(PGresult *res);

              Immediately following a PQexec or PQgetResult call,
              PQerrorMessage (on the connection) will return  the
              same   string   as   PQresultErrorMessage  (on  the
              result).  However, a PGresult will retain its error
              message  until  destroyed, whereas the connection's
              error message will change  when  subsequent  opera-
              tions  are done.  Use PQresultErrorMessage when you
              want to know the status associated with a  particu-
              lar  PGresult;  use PQerrorMessage when you want to
              know the status from the latest  operation  on  the
              connection.

              PQntuples  returns the number of tuples (instances)
              in the query result.

              int PQntuples(PGresult *res);

              PQnfields returns the number of fields (attributes)
              in the query result.
              int PQnfields(PGresult *res);

              PQfname  returns the field (attribute) name associ-
              ated with the given  field  index.   Field  indices
              start at 0.
              char *PQfname(PGresult *res,
                           int field_index);

              PQfnumber returns the field (attribute) index asso-
              ciated with the given field name.
              int PQfnumber(PGresult *res,
                           char* field_name);

              PQftype returns the field type associated with  the
              given  field  index.  The  integer  returned  is an
              internal coding of the type.  Field  indices  start
              at 0.
              Oid PQftype(PGresult *res,
                          int field_num);

              PQfsize  returns  the  size  in  bytes of the field
              associated with the given field index. If the  size
              returned  is  -1,  the  field  is a variable length
              field.  Field indices start at 0.
              short PQfsize(PGresult *res,
                            int field_index);

              PQfmod returns the type-specific modification  data
              of the field associated with the given field index.
              Field indices start at 0.
              int PQfmod(PGresult *res,
                         int field_index);

              PQgetvalue returns  the  field  (attribute)  value.
              For  most queries, the value returned by PQgetvalue
              is a null-terminated ASCII string representation of
              the  attribute value.  If the query was a result of
              a BINARY cursor, then the value returned by  PQget-
              value  is  the binary representation of the type in
              the internal format of the backend server.   It  is
              the programmer's responsibility to cast and convert
              the data to the correct C type.  The value returned
              by PQgetvalue points to storage that is part of the
              PGresult structure.  One must explicitly  copy  the
              value  into  other storage if it is to be used past
              the lifetime of the PGresult structure itself.
              char* PQgetvalue(PGresult *res,
                               int tup_num,
                               int field_num);

              PQgetlength  returns  the   length   of   a   field
              (attribute)  in  bytes.   If  the field is a struct
              varlena , the length returned here does not include
              the  size field of the varlena, i.e., it is 4 bytes
              less.
              int PQgetlength(PGresult *res,
                              int tup_num,
                              int field_num);

              PQgetisnull returns the NULL status of a field.
              int PQgetisnull(PGresult *res,
                              int tup_num,
                              int field_num);

       PQcmdStatus

              Returns the command status associated with the last
              query command.
              char *PQcmdStatus(PGresult *res);

       PQcmdTuples

              Returns  the  number of tuples (instances) affected
              by INSERT, UPDATE, and DELETE queries.
              char *PQcmdTuples(PGresult *res);

       PQoidStatus

              Returns a string with the object id  of  the  tuple
              inserted  if  the  last query is an INSERT command.
              Otherwise, returns an empty string.
              char* PQoidStatus(PGresult *res);

       PQprint

              + Prints out all the tuples in an intelligent  man-
              ner.  The psql + program uses this function for its
              output.
              void PQprint(
                    FILE* fout,      /* output stream */
                    PGresult* res,   /* query results */
                    PQprintOpt *ps   /* option structure */
                      );

              PQprintOpt is a  typedef'ed  structure  as  defined
              below.  typedef struct _PQprintOpt {
                  bool  header;           /* print table headings
              and row count */
                  bool align;            /* fill align the fields
              */
                  bool standard;         /* old brain dead format
              (needs align) */
                  bool html3;            /* output html3+  tables
              */
                  bool expanded;         /* expand tables */
                  bool  pager;             /* use pager if needed
              */
                  char *fieldSep;        /* field separator */
                  char *caption;         /*  html  table  caption
              (or NULL) */
                  char **fieldName;      /* null terminated array
              of field names (or NULL) */ } PQprintOpt;

       PQclear

              Frees the storage  associated  with  the  PGresult.
              Every query result should be properly freed when it
              is no longer used.  Failure to do this will  result
              in  memory  leaks in the frontend application.  The
              PQresult* passed in should  be  a  value  which  is
              returned  from  PQexec().   Calling PQclear() on an
              uninitialized PQresult  pointer  will  very  likely
              result in a core dump.
              void PQclear(PQresult *res);

Asynchronous Query Processing
       The  PQexec function is adequate for submitting queries in
       simple synchronous applications.  It has a couple of major
       deficiencies however:

              PQexec  waits  for  the query to be completed.  The
              application may have other  work  to  do  (such  as
              maintaining  a  user  interface),  in which case it
              won't want to block waiting for the response.

              Since control is buried inside PQexec, it  is  hard
              for  the frontend to decide it would like to try to
              cancel the ongoing query.  (It can be done  from  a
              signal handler, but not otherwise.)

              PQexec  can return only one PGresult structure.  If
              the submitted query string  contains  multiple  SQL
              commands,  all  but the last PGresult are discarded
              by PQexec.

       Applications  that  do  not  like  these  limitations  can
       instead  use the underlying functions that PQexec is built
       from: PQsendQuery and PQgetResult.

       PQsendQuery

              Submit a query to Postgres without waiting for  the
              result(s).   TRUE is returned if the query was suc-
              cessfully dispatched, FALSE if not (in which  case,
              use  PQerrorMessage  to  get more information about
              the failure).
              int PQsendQuery(PGconn *conn,
                              const char *query);
              After successfully calling PQsendQuery, call  PQge-
              tResult  one  or  more  times  to  obtain the query
              results.  PQsendQuery may not be called  again  (on
              the same connection) until PQgetResult has returned
              NULL, indicating that the query is done.

       PQgetResult

              Wait for the next result from a prior  PQsendQuery,
              and  return it.  NULL is returned when the query is
              complete and there will be no more results.
              PGresult *PQgetResult(PGconn *conn);
              PQgetResult must  be  called  repeatedly  until  it
              returns  NULL,  indicating  that the query is done.
              (If called when no  query  is  active,  PQgetResult
              will  just  return  NULL  at  once.)  Each non-null
              result from PQgetResult should be  processed  using
              the  same  PGresult  accessor  functions previously
              described.  Don't forget to free each result object
              with PQclear when done with it.  Note that PQgetRe-
              sult will block only if a query is active  and  the
              necessary  response  data  has not yet been read by
              PQconsumeInput.

       Using PQsendQuery and PQgetResult solves one  of  PQexec's
       problems:  if  a  query  string contains multiple SQL com-
       mands, the results of those commands can be obtained indi-
       vidually.   (This  allows a simple form of overlapped pro-
       cessing, by the way: the  frontend  can  be  handling  the
       results of one query while the backend is still working on
       later queries in the same query string.)  However, calling
       PQgetResult  will  still cause the frontend to block until
       the backend completes the next SQL command.  This  can  be
       avoided by proper use of three more functions:

       PQconsumeInput

              If input is available from the backend, consume it.
              void PQconsumeInput(PGconn *conn);
              No direct return value  is  available  from  PQcon-
              sumeInput,  but  after  calling it, the application
              may check PQisBusy  and/or  PQnotifies  to  see  if
              their  state  has  changed.   PQconsumeInput may be
              called even if the application is not  prepared  to
              deal  with  a  result or notification just yet.  It
              will read available data and save it in  a  buffer,
              thereby  causing  a select(2) read-ready indication
              to go away.  The application can  thus  use  PQcon-
              sumeInput  to  clear  the  select condition immedi-
              ately, and then examine the results at leisure.

       PQisBusy

              Returns TRUE if a query is busy, that is,  PQgetRe-
              sult would block waiting for input.  A FALSE return
              indicates  that  PQgetResult  can  be  called  with
              assurance of not blocking.
              int PQisBusy(PGconn *conn);
              PQisBusy  will not itself attempt to read data from
              the  backend;  therefore  PQconsumeInput  must   be
              invoked first, or the busy state will never end.

       PQsocket

              Obtain  the  file descriptor number for the backend
              connection socket.  A valid descriptor will  be  >=
              0; a result of -1 indicates that no backend connec-
              tion is currently open.
              int PQsocket(PGconn *conn);
              PQsocket should  be  used  to  obtain  the  backend
              socket  descriptor  in  preparation  for  executing
              select(2).  This allows an application to wait  for
              either  backend  responses or other conditions.  If
              the result of select(2) indicates that data can  be
              read  from  the backend socket, then PQconsumeInput
              should be called to read  the  data;  after  which,
              PQisBusy,  PQgetResult,  and/or  PQnotifies  can be
              used to process the response.

       A typical frontend using these functions will have a  main
       loop  that  uses  select(2) to wait for all the conditions
       that it must respond to.  One of the  conditions  will  be
       input  available from the backend, which in select's terms
       is readable data on  the  file  descriptor  identified  by
       PQsocket.   When  the  main  loop  detects input ready, it
       should call PQconsumeInput to read the input.  It can then
       call PQisBusy, followed by PQgetResult if PQisBusy returns
       FALSE.  It can also call PQnotifies to detect NOTIFY  mes-
       sages (see "Asynchronous Notification", below).

       A  frontend  that  uses  PQsendQuery/PQgetResult  can also
       attempt to cancel a query that is still being processed by
       the backend.

       PQrequestCancel

              Request   that  <ProductName>Postgres</ProductName>
              abandon processing of the current query.
              int PQrequestCancel(PGconn *conn);
              The return value is TRUE if the cancel request  was
              successfully  dispatched,  FALSE  if not.  (If not,
              PQerrorMessage tells why not.)  Successful dispatch
              is  no  guarantee  that  the  request will have any
              effect, however.  Regardless of the return value of
              PQrequestCancel, the application must continue with
              the normal result-reading sequence  using  PQgetRe-
              sult.   If  the cancellation is effective, the cur-
              rent query will terminate early and return an error
              result.  If the cancellation fails (say because the
              backend was already  done  processing  the  query),
              then there will be no visible result at all.

       Note  that  if the current query is part of a transaction,
       cancellation will abort the whole transaction.

       PQrequestCancel can safely be invoked from a  signal  han-
       dler.   So,  it  is also possible to use it in conjunction
       with plain PQexec, if the decision to cancel can  be  made
       in a signal handler.  For example, psql invokes PQrequest-
       Cancel from a SIGINT signal handler, thus allowing  inter-
       active  cancellation  of  queries  that  it issues through
       PQexec.  Note that PQrequestCancel will have no effect  if
       the connection is not currently open or the backend is not
       currently processing a query.

Fast Path
       Postgres provides a fast path interface to  send  function
       calls  to  the  backend.   This  is a trapdoor into system
       internals and can be  a  potential  security  hole.   Most
       users will not need this feature.
       PGresult* PQfn(PGconn* conn,
                   int fnid,
                   int *result_buf,
                   int *result_len,
                   int result_is_int,
                   PQArgBlock *args,
                   int nargs);

       The fnid argument is the object identifier of the function
       to be executed.  result_buf is the buffer in which to load
       the  return  value.  The caller must have allocated suffi-
       cient space to store the return value.  The result  length
       will  be returned in the storage pointed to by result_len.
       If  the  result  is  to  be   an   integer   value,   than
       result_is_int  should  be set to 1; otherwise it should be
       set to 0.  args and nargs specify  the  arguments  to  the
       function.
       typedef struct {
           int len;
           int isint;
           union {
               int *ptr;
            int integer;
           } u;
       } PQArgBlock;

       PQfn  always  returns a valid PGresult*.  The resultStatus
       should be checked before the result is used.   The  caller
       is  responsible for freeing the PGresult with PQclear when
       it is no longer needed.

Asynchronous Notification
       Postgres supports asynchronous notification via the LISTEN
       and  NOTIFY commands.  A backend registers its interest in
       a particular notification condition with the  LISTEN  com-
       mand.   All  backends  listening on a particular condition
       will be notified asynchronously when a NOTIFY of that con-
       dition  name  is  executed  by any backend.  No additional
       information is passed from the notifier to  the  listener.
       Thus, typically, any actual data that needs to be communi-
       cated is transferred through a  database  relation.   Com-
       monly  the  condition  name  is the same as the associated
       relation, but it is not necessary  for  there  to  be  any
       associated relation.

       libpq  applications submit LISTEN commands as ordinary SQL
       queries.  Subsequently, arrival of NOTIFY messages can  be
       detected by calling PQnotifies().

       PQNotifies

              Returns   the  next  notification  from  a  list of
              unhandled notification messages received  from  the
              backend.   Returns  NULL  if  there  are no pending
              notifications.  PQnotifies behaves like the popping
              of  a  stack.  Once a notification is returned from
              PQnotifies, it is considered handled  and  will  be
              removed from the list of notifications.
              PGnotify* PQNotifies(PGconn *conn);
              After  processing  a  PGnotify  object  returned by
              PQnotifies, be sure to free it with free() to avoid
              a memory leak.

       The  second  sample program gives an example of the use of
       asynchronous notification.

       PQnotifies() does not actually read backend data; it  just
       returns  messages  previously  absorbed  by  another libpq
       function.  In prior releases of libpq,  the  only  way  to
       ensure timely receipt of NOTIFY messages was to constantly
       submit queries, even empty ones, and  then  check  PQnoti-
       fies() after each PQexec().  While this still works, it is
       deprecated as a waste of processing power.  A  better  way
       to  check  for  NOTIFY  messages  when  you have no useful
       queries to make is to call  PQconsumeInput(),  then  check
       PQnotifies().   You  can use select(2) to wait for backend
       data to arrive, thereby using no CPU power unless there is
       something  to do.  Note that this will work OK whether you
       use  PQsendQuery/PQgetResult  or  plain  old  PQexec   for
       queries.   You  should, however, remember to check PQnoti-
       fies() after each PQgetResult or  PQexec  to  see  if  any
       notifications  came in during the processing of the query.

Functions Associated with the COPY Command
       The copy command in Postgres has options to read  from  or
       write to the network connection used by Libpq.  Therefore,
       functions are necessary to access this network  connection
       directly  so  applications may take full advantage of this
       capability.

       These functions should be executed only after obtaining  a
       PGRES_COPY_OUT  or PGRES_COPY_IN result object from PQexec
       or PQgetResult.

       PQgetline

              Reads  a  newline-terminated  line  of   characters
              (transmitted  by  the backend server) into a buffer
              string of size length .  Like fgets(3),  this  rou-
              tine  copies up to length -1 characters into string
              .  It is like gets(3), however, in that it converts
              the terminating newline into a null character.

              PQgetline  returns EOF at EOF, 0 if the entire line
              has been read, and 1 if the buffer is full but  the
              terminating newline has not yet been read.

              Notice  that the application must check to see if a
              new line consists of the two characters "\.", which
              indicates  that  the  backend  server  has finished
              sending the results of the  copy  command.   There-
              fore,  if  the  application ever expects to receive
              lines that are more than length -1 characters long,
              the  application  must  be sure to check the return
              value of PQgetline very carefully.

              The code in
              ../src/bin/psql/psql.c
              contains routines that correctly  handle  the  copy
              protocol.
              int PQgetline(PGconn *conn,
                            char *string,
                            int length)

       PQputline

              Sends  a  null-terminated  string  to  the  backend
              server.

              The application must explicitly send the two  char-
              acters  "\."   on  a  final line to indicate to the
              backend that it has finished sending its data.
              void PQputline(PGconn *conn,
                             char *string);

       PQendcopy

              Syncs with the backend.  This function waits  until
              the  backend  has  finished  the  copy.   It should
              either be issued when the last string has been sent
              to  the  backend  using  PQputline or when the last
              string has been received  from  the  backend  using
              PGgetline  .   It must be issued or the backend may
              get "out of sync" with the frontend.   Upon  return
              from this function, the backend is ready to receive
              the next query.

              The return value is  0  on  successful  completion,
              nonzero otherwise.
              int PQendcopy(PGconn *conn);
              As an example:
              PQexec(conn, "create table foo (a int4, b char(16), d float8)");
              PQexec(conn, "copy foo from stdin");
              PQputline(conn, "3\thello world\t4.5\n");
              PQputline(conn,"4\tgoodbye world\t7.11\n");
              ...
              PQputline(conn,"\\.\n");
              PQendcopy(conn);

       When  using PQgetResult, the application should respond to
       a PGRES_COPY_OUT result by executing PQgetline repeatedly,
       followed  by  PQendcopy after the terminator line is seen.
       It should then return to the PQgetResult loop until  PQge-
       tResult returns NULL.  Similarly a PGRES_COPY_IN result is
       processed by a  series  of  PQputline  calls  followed  by
       PQendcopy,  then  return  to  the  PQgetResult loop.  This
       arrangement will ensure that a copy in or copy out command
       embedded in a series of SQL commands will be executed cor-
       rectly.  Older applications are likely to submit a copy in
       or  copy out via PQexec and assume that the transaction is
       done after PQendcopy.  This will work  correctly  only  if
       the  copy  in/out  is  the  only  SQL command in the query
       string.

LIBPQ Tracing Functions
       PQtrace

              Enable tracing of the  frontend/backend  communica-
              tion to a debugging file stream.
              void PQtrace(PGconn *conn
                           FILE *debug_port)

       PQuntrace

              Disable tracing started by PQtrace
              void PQuntrace(PGconn *conn)

LIBPQ Control Functions
       PQsetNoticeProcessor

              Control  reporting  of  notice and warning messages
              generated by libpq.
              void PQsetNoticeProcessor (PGconn * conn,
                        void (*noticeProcessor) (void * arg, const char * message),
                        void * arg)
              By default, libpq prints "notice" messages from the
              backend  on stderr, as well as a few error messages
              that it generates by itself.  This behavior can  be
              overridden  by  supplying  a callback function that
              does something else with the messages.   The  call-
              back  function is passed the text of the error mes-
              sage (which includes a trailing  newline),  plus  a
              void  pointer that is the same one passed to PQset-
              NoticeProcessor.  (This  pointer  can  be  used  to
              access  application-specific state if needed.)  The
              default notice processor is simply
              static void
              defaultNoticeProcessor(void * arg, const char * message)
              {
                   fprintf(stderr, "%s", message);
              }
              To use a special notice processor, call  PQsetNoti-
              ceProcessor just after any creation of a new PGconn
              object.

User Authentication Functions
       If the user has generated the  appropriate  authentication
       credentials  (e.g., obtaining Kerberos tickets), the fron-
       tend/backend authentication process is handled  by  PQexec
       without  any  further  intervention.   The  authentication
       method  is  now  determined  entirely  by  the  DBA   (see
       pga_hba.conf(5)).   The  following routines no longer have
       any effect and should not be used.

       fe_getauthname

              Returns a pointer to static space containing  what-
              ever  name the user has authenticated.  Use of this
              routine in place of calls  to  getenv(3)  or  getp-
              wuid(3)  by  applications is highly recommended, as
              it is entirely possible that the authenticated user
              name  is not the same as value of the USER environ-
              ment variable or the user's entry in /etc/passwd  .
              char *fe_getauthname(char* errorMessage)

       fe_setauthsvc

              Specifies that Libpq should use authentication ser-
              vice name  rather  than  its  compiled-in  default.
              This  value  is typically taken from a command-line
              switch.
              void fe_setauthsvc(char *name,
                                 char* errorMessage)
              Any error messages from the authentication attempts
              are returned in the errorMessage argument.

BUGS
       The query buffer is 8192 bytes long, and queries over that
       length will be rejected.

Sample Programs
Sample Program 1
       /*
        * testlibpq.c
        *  Test the C version of Libpq, the Postgres frontend library.
        *
        *
        */
       #include <stdio.h>
       #include "libpq-fe.h"

       void
       exit_nicely(PGconn *conn)
       {
           PQfinish(conn);
           exit(1);
       }

       main()
       {
           char       *pghost,
                      *pgport,
                      *pgoptions,
                      *pgtty;
           char       *dbName;
           int         nFields;
           int         i,
                       j;

       /*  FILE *debug; */

           PGconn     *conn;
           PGresult   *res;

           /*
            * begin, by setting the parameters for a backend connection if the
            * parameters are null, then the system will try to use reasonable
            * defaults by looking up environment variables or, failing that,
            * using hardwired constants
            */
           pghost = NULL;              /* host name of the backend server */
           pgport = NULL;              /* port of the backend server */
           pgoptions = NULL;           /* special options to start up the backend
                                        * server */
           pgtty = NULL;               /* debugging tty for the backend server */
           dbName = "template1";

           /* make a connection to the database */
           conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

           /* check to see that the backend connection was successfully made */
           if (PQstatus(conn) == CONNECTION_BAD)
           {
               fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
               fprintf(stderr, "%s", PQerrorMessage(conn));
               exit_nicely(conn);
           }

       /*  debug = fopen("/tmp/trace.out","w");  */
       /*   PQtrace(conn, debug);  */

           /* start a transaction block */
           res = PQexec(conn, "BEGIN");
           if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
           {
               fprintf(stderr, "BEGIN command failed\n");
               PQclear(res);
               exit_nicely(conn);
           }

           /*
            * should PQclear PGresult whenever it is no longer needed to avoid
            * memory leaks
            */
           PQclear(res);

           /*
            * fetch instances from the pg_database, the system catalog of
            * databases
            */
           res = PQexec(conn, "DECLARE mycursor CURSOR FOR select * from pg_database");
           if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
           {
               fprintf(stderr, "DECLARE CURSOR command failed\n");
               PQclear(res);
               exit_nicely(conn);
           }
           PQclear(res);

           res = PQexec(conn, "FETCH ALL in mycursor");
           if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
           {
               fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
               PQclear(res);
               exit_nicely(conn);
           }

           /* first, print out the attribute names */
           nFields = PQnfields(res);
           for (i = 0; i < nFields; i++)
               printf("%-15s", PQfname(res, i));
           printf("\n\n");

           /* next, print out the instances */
           for (i = 0; i < PQntuples(res); i++)
           {
               for (j = 0; j < nFields; j++)
                   printf("%-15s", PQgetvalue(res, i, j));
               printf("\n");
           }

           PQclear(res);

           /* close the cursor */
           res = PQexec(conn, "CLOSE mycursor");
           PQclear(res);

           /* commit the transaction */
           res = PQexec(conn, "COMMIT");
           PQclear(res);

           /* close the connection to the database and cleanup */
           PQfinish(conn);

       /*   fclose(debug); */
       }

PostgreSQL                   08/08/98                           1

LIBPQ(INTRO)                PostgreSQL               LIBPQ(INTRO)

Sample Program 2
       /*
        * testlibpq2.c
        *  Test of the asynchronous notification interface
        *
        * Start this program, then from psql in another window do
        *   NOTIFY TBL2;
        *
        * Or, if you want to get fancy, try this:
        * Populate a database with the following:
        *
        *   CREATE TABLE TBL1 (i int4);
        *
        *   CREATE TABLE TBL2 (i int4);
        *
        *   CREATE RULE r1 AS ON INSERT TO TBL1 DO
        *     (INSERT INTO TBL2 values (new.i); NOTIFY TBL2);
        *
        * and do
        *
        *   INSERT INTO TBL1 values (10);
        *
        */
       #include <stdio.h>
       #include "libpq-fe.h"

       void
       exit_nicely(PGconn *conn)
       {
           PQfinish(conn);
           exit(1);
       }

       main()
       {
           char       *pghost,
                      *pgport,
                      *pgoptions,
                      *pgtty;
           char       *dbName;
           int         nFields;
           int         i,
                       j;

           PGconn     *conn;
           PGresult   *res;
           PGnotify   *notify;

           /*
            * begin, by setting the parameters for a backend connection if the
            * parameters are null, then the system will try to use reasonable
            * defaults by looking up environment variables or, failing that,
            * using hardwired constants
            */
           pghost = NULL;              /* host name of the backend server */
           pgport = NULL;              /* port of the backend server */
           pgoptions = NULL;           /* special options to start up the backend
                                        * server */
           pgtty = NULL;               /* debugging tty for the backend server */
           dbName = getenv("USER");    /* change this to the name of your test
                                        * database */

           /* make a connection to the database */
           conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

           /* check to see that the backend connection was successfully made */
           if (PQstatus(conn) == CONNECTION_BAD)
           {
               fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
               fprintf(stderr, "%s", PQerrorMessage(conn));
               exit_nicely(conn);
           }

           res = PQexec(conn, "LISTEN TBL2");
           if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
           {
               fprintf(stderr, "LISTEN command failed\n");
               PQclear(res);
               exit_nicely(conn);
           }

           /*
            * should PQclear PGresult whenever it is no longer needed to avoid
            * memory leaks
            */
           PQclear(res);

           while (1)
           {
            /* wait a little bit between checks;
             * waiting with select() would be more efficient.
             */
            sleep(1);
            /* collect any asynchronous backend messages */
            PQconsumeInput(conn);
            /* check for asynchronous notify messages */
            while ((notify = PQnotifies(conn)) != NULL) {
                   fprintf(stderr,
                       "ASYNC NOTIFY of '%s' from backend pid '%d' received\n",
                           notify->relname, notify->be_pid);
                   free(notify);
               }
           }

           /* close the connection to the database and cleanup */
           PQfinish(conn);

       }

PostgreSQL                   08/08/98                           2

LIBPQ(INTRO)                PostgreSQL               LIBPQ(INTRO)

Sample Program 3
       /*
        * testlibpq3.c
        *  Test the C version of Libpq, the Postgres frontend library.
        *   tests the binary cursor interface
        *
        *
        *
        populate a database by doing the following:

       CREATE TABLE test1 (i int4, d float4, p polygon);

       INSERT INTO test1 values (1, 3.567, '(3.0, 4.0, 1.0, 2.0)'::polygon);

       INSERT INTO test1 values (2, 89.05, '(4.0, 3.0, 2.0, 1.0)'::polygon);

        the expected output is:

       tuple 0: got
        i = (4 bytes) 1,
        d = (4 bytes) 3.567000,
        p = (4 bytes) 2 points         boundbox = (hi=3.000000/4.000000, lo = 1.000000,2.000000)
       tuple 1: got
        i = (4 bytes) 2,
        d = (4 bytes) 89.050003,
        p = (4 bytes) 2 points         boundbox = (hi=4.000000/3.000000, lo = 2.000000,1.000000)

        *
        */
       #include <stdio.h>
       #include "libpq-fe.h"
       #include "utils/geo-decls.h"    /* for the POLYGON type */

       void
       exit_nicely(PGconn *conn)
       {
           PQfinish(conn);
           exit(1);
       }

       main()
       {
           char       *pghost,
                      *pgport,
                      *pgoptions,
                      *pgtty;
           char       *dbName;
           int         nFields;
           int         i,
                       j;
           int         i_fnum,
                       d_fnum,
                       p_fnum;

           PGconn     *conn;
           PGresult   *res;

           /*
            * begin, by setting the parameters for a backend connection if the
            * parameters are null, then the system will try to use reasonable
            * defaults by looking up environment variables or, failing that,
            * using hardwired constants
            */
           pghost = NULL;              /* host name of the backend server */
           pgport = NULL;              /* port of the backend server */
           pgoptions = NULL;           /* special options to start up the backend
                                        * server */
           pgtty = NULL;               /* debugging tty for the backend server */

           dbName = getenv("USER");    /* change this to the name of your test
                                        * database */

           /* make a connection to the database */
           conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbName);

           /* check to see that the backend connection was successfully made */
           if (PQstatus(conn) == CONNECTION_BAD)
           {
               fprintf(stderr, "Connection to database '%s' failed.\n", dbName);
               fprintf(stderr, "%s", PQerrorMessage(conn));
               exit_nicely(conn);
           }

           /* start a transaction block */
           res = PQexec(conn, "BEGIN");
           if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
           {
               fprintf(stderr, "BEGIN command failed\n");
               PQclear(res);
               exit_nicely(conn);
           }

           /*
            * should PQclear PGresult whenever it is no longer needed to avoid
            * memory leaks
            */
           PQclear(res);

           /*
            * fetch instances from the pg_database, the system catalog of
            * databases
            */
           res = PQexec(conn, "DECLARE mycursor BINARY CURSOR FOR select * from test1");
           if (!res || PQresultStatus(res) != PGRES_COMMAND_OK)
           {
               fprintf(stderr, "DECLARE CURSOR command failed\n");
               PQclear(res);
               exit_nicely(conn);
           }
           PQclear(res);

           res = PQexec(conn, "FETCH ALL in mycursor");
           if (!res || PQresultStatus(res) != PGRES_TUPLES_OK)
           {
               fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
               PQclear(res);
               exit_nicely(conn);
           }

           i_fnum = PQfnumber(res, "i");
           d_fnum = PQfnumber(res, "d");
           p_fnum = PQfnumber(res, "p");

           for (i = 0; i < 3; i++)
           {
               printf("type[%d] = %d, size[%d] = %d\n",
                      i, PQftype(res, i),
                      i, PQfsize(res, i));
           }
           for (i = 0; i < PQntuples(res); i++)
           {
               int        *ival;
               float      *dval;
               int         plen;
               POLYGON    *pval;

               /* we hard-wire this to the 3 fields we know about */
               ival = (int *) PQgetvalue(res, i, i_fnum);
               dval = (float *) PQgetvalue(res, i, d_fnum);
               plen = PQgetlength(res, i, p_fnum);

               /*
                * plen doesn't include the length field so need to increment by
                * VARHDSZ
                */
               pval = (POLYGON *) malloc(plen + VARHDRSZ);
               pval->size = plen;
               memmove((char *) &pval->npts, PQgetvalue(res, i, p_fnum), plen);
               printf("tuple %d: got\n", i);
               printf(" i = (%d bytes) %d,\n",
                      PQgetlength(res, i, i_fnum), *ival);
               printf(" d = (%d bytes) %f,\n",
                      PQgetlength(res, i, d_fnum), *dval);
               printf(" p = (%d bytes) %d points \tboundbox = (hi=%f/%f, lo = %f,%f)\n",
                      PQgetlength(res, i, d_fnum),
                      pval->npts,
                      pval->boundbox.xh,
                      pval->boundbox.yh,
                      pval->boundbox.xl,
                      pval->boundbox.yl);
           }

           PQclear(res);

           /* close the cursor */
           res = PQexec(conn, "CLOSE mycursor");
           PQclear(res);

           /* commit the transaction */
           res = PQexec(conn, "COMMIT");
           PQclear(res);

           /* close the connection to the database and cleanup */
           PQfinish(conn);

       }

PostgreSQL                   08/08/98                           3