ביצוע שאילתה מ PG ב C

איך לקבל נתונים מ PG באמצעות שימוש ב libpq:

#include <stdio.h>
#include <string.h>
#include <postgresql/libpq-fe.h>

void usage();

int main(int argc , char * argv[])
{
  PGconn          *db_connection;
  PGresult        *connection_result;
  const  char *    connection_info;
  PGresult        *request_result;
  int              number_of_records;
  int              number_of_fields;
  int              column_index;//column index in record 
  int              record_index;//record index under process

  db_connection  = NULL;
  connection_result = NULL;
  request_result = NULL;

  if (argc != 2) 
  {
    usage (argv[0]);
    return 1;
  }
  connection_info = argv[1];
  db_connection = PQconnectdb(connection_info);
  if (NULL == db_connection)
  {
    printf("%s:%d Failed connection to %s\n",
            __FUNCTION__,__LINE__, connection_info);
    return 1;
  }

  if (CONNECTION_BAD == PQstatus(db_connection) )
  {
     printf("%s:%d bad connection to %s=>%s\n",
             __FUNCTION__,__LINE__, 
             connection_info,
             PQerrorMessage(db_connection));

     goto error;
  }
  //execute over a db connection, the executation may fail or success
  request_result =  PQexec(
                           db_connection,
                           "SELECT field1 FROM mytable where field2=value");
  if (NULL == request_result)
  {
    printf("%s:%d Failed to execute command with error: %s\n",
           __FUNCTION__,__LINE__, PQerrorMessage(db_connection));
    goto error;
  }
  //checing here for PGRES_TUPLES_OK and not PGRES_COMMAND_OK because the request need to return data
  if (PQresultStatus(request_result) != PGRES_TUPLES_OK)
  {
     printf("%s:%d Failed to execute command with error : %s\n",__FUNCTION__,__LINE__, PQerrorMessage(db_connection));
     goto error;
  }
  number_of_records  = PQntuples(request_result);
  number_of_fields   = PQnfields(request_result);

  if (0 >   number_of_fields)
  {
     printf("%s:%d Invalid number of fields in result set (%d) : %s\n",__FUNCTION__,__LINE__, 
     number_of_fields,
     PQerrorMessage(db_connection));

     goto error;
  }
 
  printf("Result set have %d records and %d fields each\n,number_of_records,number_of_fields);

  for (record_index = 0 ; record_index < number_of_records;record_index++)
  {
    printf("record %d/%d:\n",record_index,number_of_records);
    for (column_index = 0 ; column_index < number_of_fields;column_index++)
    {
      printf("%d/%d field value <%s>\n",PQgetvalue(request_result, record_index, column_index));
    }
    printf("\n");
  }
  PQfinish (db_connection);
  return 0;

error:
  if (NULL != request_result)
  {
    PQclear(request_result);
  }
  if (NULL != connection_result)
  {
    PQclear(connection_result);
  }
  if (NULL!= db_connection) 
  {
    PQfinish (db_connection);
  }
  return 1;

}

void usage(const char * program_name)
{
  printf("USAGE: %s connection_string\n"
         "connection_string := keyValue|URI\n",
         "URI example:\n"
         "postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp&client_encoding=UTF8\n",
         program_name);
}