libpq-fe: how to determine unique collision ? - Mailing list pgsql-general

Hi,

I am using libpq (with postgreSQL Version: 6.5.3-23 (Debian 2.2r2)). I
have the following questions which, I am afraid, are not answered in the
documentation:

   - how do you detect the error `Can't insert tuple because duplicate' ?

   - how do you get the OID of an insertion (the goal being to get
     the value for the SERIAL type, in order to do something with it)  ?

Thank you.

Example:
   CREATE TABLE file (id SERIAL,
                      creation_time TIMESTAMP
                         NOT NULL DEFAULT CURRENT_TIMESTAMP,
                      last_access_time TIMESTAMP, /* read some data */
                      file_name VARCHAR(100) NOT NULL, /* see servers_defs.h */
                      client_name VARCHAR(30) NOT NULL, /* from auth */
                      server_name VARCHAR(30) NOT NULL, /* from auth */
                      UNIQUE(client_name, server_name, file_name),
                      UNIQUE(id), PRIMARY KEY(id));

#include "libpq-fe.h"

#define BUFFER_LENGTH 1024 /* Is boundary-checked */
BOOL db_create_file_tuple(db_context_t context,
                          const char *file_name,
                          const char *client_name,
                          const char *server_name,
                          db_error_t *error) {
   char buffer[BUFFER_LENGTH];
   BOOL result = FALSE;
   PGconn *conn = (PGconn *) context;

   *error = DB_ERROR_INTERNAL;

   if (snprintf(buffer,
                sizeof(buffer),
                "INSERT INTO file (file_name, client_name, server_name)"
                " VALUES('%s', '%s', '%s')",
                file_name,
                client_name,
                server_name)
       == -1) {
      *error = DB_ERROR_WOULD_OVERFLOW;
   }
   else {
      PGresult *res;

      /* BUGS
       *    - Should protect or bind like in Perl.
       */

      res = PQexec(conn, buffer);
      if (res && (PQresultStatus(res) == PGRES_COMMAND_OK)) {
         result = TRUE;
         *error = DB_ERROR_NONE; /* not really useful, shouldn't be read */
      }
      else {
         debug_log(DEBUG_LEVEL_ERROR,
                   "INSERT query failed: %s",
                   PQerrorMessage(conn));

         /* BUGS
          *    - We are not sure this is it, but we can only guess
          *      for now.
          */
         *error = DB_ERROR_FILE_OBJECT_EXISTS;
      }

      PQclear(res); /* avoid memory leaks */
   }

   return result;
}