Accessing array elements in a Postges tables
                      through the libpq interface

			 Akbar Mokhtarani LBNL
			 (amokhtarani@lbl.gov)		
 
The PQgetvalue(res, row, field) function returns a char* to the beginning of
the memory  where the array is stored. It is up to the user to walk the
memory space and extract the elements. After few days of head scraching and 
searching the documentation, I came accross the following comment in
  src/backend/utils/adt/arrayfuncs.c:

  /*----------
   * A standard varlena array has the following internal structure:
   *        <size>                - total number of bytes (also, TOAST info flags)
   *        <ndim>                - number of dimensions of the array
   *        <flags>               - bit mask of flags
   *        <elemtype>    - element type OID
   *        <dim>                 - size of each array axis (C array of int)
   *        <dim_lower>   - lower boundary of each dimension (C array of int)
   *        <actual data> - whatever is the stored data
   * The actual data starts on a MAXALIGN boundary.  Individual items in the
   * array are aligned as specified by the array element type.
   *

  This didn't quite agree with what I found but it gave me a starting point.
  Here is what I found and hope experts will correct me if I am wrong:

  The first 20 bytes seem to have the following information about the array:
  --first 4 bytes    don't know what it is but it is always 1
  --second 4 bytes    "     "    "    "  " "    "  "   "    0
  --third 4 bytes    oid of the datatype in the array
  --4th 4 bytes      number of  elements in the array
  --5th 4 bytes      dimension of the array

  Form here we have the actual data. Each datum is preceeded with a 4 byte 
  integer indicating the number of bytes the next element occupies. Knowing 
  the number of elements one can parse the memory and get access to the 
  elements.

  I wrote a small program to test the above conjecture. I was able to walk 
  through some basic data types (int, float, float8, char*, char**,
  timestamp ). I was not able to get access to numeric type. 

  Note:The timestamp elements come back in number of seconds 
  from 1-1-2000. Need a utility function to convert it to a more 
  readable format. There must such a function somewhere in the source, but
  I haven't found it yet.

  Here is the test program and table schema I used to test it:
Note: I was not formally trained as a software engineer. I found my 
way to here through physics, so forgive me if the code doesn't stand 
up to seasoned programmers' standard.

/*
 * testArray.c
 *
 *  test program to access array elements through the libpq interface 
 *
 *  tested with PostgreSQL 7.4.5
 */

/* use the following table to test the program
drop table myarray;
create table myarray(
	name text,
	intarray  int[],
	floatarray  real[],
	chararray   char[],
	textarray   text[],
	numarray    numeric[],
	t1  timestamp  default '-infinity',
	t2  timestamp   default 'infinity',
	doublearray   float8[],
	timearray   timestamp[] default '{-infinity, infinity}'
);

INSERT INTO myarray VALUES('First', '{4,5,3,2,6}', '{2.3,3.5}',
      '{"C", "F","D"}', '{"One", "Two", "Three"}', '{45.3, 67.8}',
	 'Jan-1-2004', 'Feb-2-2004', '{ 5691.3456, 6798.986, 3435.786}',
	'{Mar-8-2001, Jun-15-2003}');
INSERT INTO myarray VALUES('Second', '{4,2,6}', '{2.3,3.5, 6.7}',
      '{"C", "F","D", "G"}', '{ "Two", "Three"}', '{56.89,45.3, 67.8}',
	'Feb-2-1999', 'Mar-4-2000','{ 456.456, 607.9686, 3145.786}');
INSERT INTO myarray VALUES('Third', null, '{1.4,2.3,3.5}',
      '{"C", "F","D"}', '{"One", "Two", "Three","Four"}', '{45.3, 67.8}'
	,'Feb-2-2000', 'Mar-4-2001','{ 256.3456, 167.986, 35.786}' );

*/

#include <algorithm>
#include <stdio.h>
#include <stdlib.h>
#include "postgresql/libpq-fe.h"
//#include "libpq-fe.h"


#include <iostream>
#include <string>
using namespace std;

#include <netinet/in.h>

static void
exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(1);
}
// a helper function to get the number of elements in an array
int getNoEle(char* m) { return ntohl(*(int*)(m + 3*sizeof(int)));}

//template function for most multi-byte data types
template <class T> void fillArray(T *&, char*);

//specialized template functions for char and string
void fillArray(char *&, char*);
void fillArray(char **&, char*);

//functions for byte swaping
#define ByteSwap(x)  byteswap((unsigned char*) &x, sizeof(x))
#define byteSwap(x,n)  byteswap((unsigned char*) &x,  n)

void byteswap(unsigned char *b, int n)
{
  register int i = 0;
  register int j = n-1;
  while (i<j)
  {
    std::swap(b[i], b[j]);
    i++, j--;
  }
}


int
main(int argc, char **argv)
{
	const char *conninfo;
	PGconn	   *conn;
	PGresult   *res;

	/*
	 * If the user supplies a parameter on the command line, use it as the
	 * conninfo string; otherwise default to setting dbname=template1 and
	 * using environment variables or defaults for all other connection
	 * parameters.
	 */
	if (argc > 1)
		conninfo = argv[1];
	else

	  conninfo = "hostaddr=127.0.0.1 dbname =test user=akbarm";


	/* Make a connection to the database */
	conn = PQconnectdb(conninfo);

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

	/*
	 * Our test case here involves using a cursor, for which we must be
	 * inside a transaction block.	We could do the whole thing with a
	 * single PQexec() of "select * from pg_database", but that's too
	 * trivial to make a good example.
	 */

	/* Start a transaction block */
	res = PQexec(conn, "BEGIN");
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
		PQclear(res);
		exit_nicely(conn);
	}

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

	/*
	 * Fetch rows from pg_database, the system catalog of databases
	 */
	string command ="DECLARE myportal BINARY CURSOR FOR select * from myarray"; 

	res = PQexec(conn,  command.c_str());
	if (PQresultStatus(res) != PGRES_COMMAND_OK)
	{
		fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
		PQclear(res);
		exit_nicely(conn);
	}
	
	PQclear(res);

	res = PQexec(conn, "FETCH ALL in myportal");
	if (PQresultStatus(res) != PGRES_TUPLES_OK)
	{
		fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
		PQclear(res);
		exit_nicely(conn);
	}
	
	for (int i = 0; i < PQntuples(res); i++)
	  {

	  char* name = PQgetvalue(res, i, 0);
	  char* intar = PQgetvalue(res, i, 1);
	  char* floatar = PQgetvalue(res, i, 2);
	  char* charar = PQgetvalue(res, i, 3);
	  char* textar = PQgetvalue(res, i, 4);
	  char* time1 = PQgetvalue(res, i, 6);
	  char* time2 = PQgetvalue(res, i, 7);
	  char* dblar = PQgetvalue(res, i, 8);
	  char* timear = PQgetvalue(res, i, 9);
	  
	  //int array
	  int* ii;
	  if(PQgetlength(res, i, 1) > 0){
	    fillArray(ii, intar);
	    for (int j=0; j<getNoEle(intar); j++)
	      cout << "  ii[" << j << "]: " << ii[j];
	    cout << endl;
	  }
	  //char array
	  char* cc;
	  if(PQgetlength(res, i, 3) > 0){
	    fillArray(cc, charar);
	    for (int j=0; j<getNoEle(charar); j++)
	      cout << "  cc[" << j << "]: " << cc[j];
	    cout << endl;
	  }
	  //float array
	  float* fl;
	  if( PQgetlength(res, i, 2)> 0){
	    fillArray(fl, floatar);
	    for (int j=0; j<getNoEle(floatar); j++)
	      cout << "  fl[" << j << "]: " << fl[j];
	    cout << endl;
	  }
	  //string array
	  char** str;
	  if( PQgetlength(res, i, 4) > 0){
	    fillArray(str, textar);
	    for (int j=0; j<getNoEle(textar); j++)
	      cout << "  str[" << j << "]: " << str[j];
	    cout << endl;
	  }
	  //array of doubles
	  double * dbl;
	  if(PQgetlength(res, i, 8) > 0){
	    fillArray(dbl, dblar);
	    for (int j=0; j<getNoEle(dblar); j++)
	      cout << "  dbl[" << j << "]: " << dbl[j];
	    cout << endl;
	  }

	  //timestamps
	  long long t1 = *(long long*) time1;
	  long long t2 = *(long long*) time2;

	  byteSwap( t1, PQgetlength(res, i, 6));
	  byteSwap( t2, PQgetlength(res, i, 7));

	  cout << "  time1: " << t1 << "  time2: " << t2 << endl;

	  //timestamp array
	  long long* ll;
	  if( PQgetlength(res, i, 9) > 0){
	    fillArray(ll, timear);
	    for (int j=0; j<getNoEle(timear); j++)
	      cout << "  time[" << j << "]: " << ll[j];
	    cout << endl;
	  }

	  }

	/* close the portal ... we don't bother to check for errors ... */
	res = PQexec(conn, "CLOSE myportal");
	PQclear(res);

	/* end the transaction */
	res = PQexec(conn, "END");
	PQclear(res);

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

	return 0;
}
template<class T>
void fillArray(T *&ar, char* mem){
  int nEle = getNoEle(mem);
  ar = new T[nEle];
  char* start = mem + 5*sizeof(int);
  int  intSize = sizeof(int);
  for(int i=0; i<nEle; i++){
    int size = ntohl(*(int*)(start));
    ar[i] = (*(T*)(start + intSize));
    byteSwap(ar[i], size);
    start += size + intSize;
  }
}
void fillArray(char *&ar, char* mem){
  int nEle = getNoEle(mem);
  ar = new char[nEle];
  char* start = mem + 5*sizeof(int);
  int  intSize = sizeof(int);
  for(int i=0; i<nEle; i++){
    int size = ntohl(*(int*)(start));
    ar[i] = *(char*)(start + intSize);
    start += size + intSize;
  }
}
void fillArray(char **&ar, char* mem){
  int nEle = getNoEle(mem);
  ar = new char*[nEle];
  char* start = mem + 5*sizeof(int);
  int  intSize = sizeof(int);
  for(int i=0; i<nEle; i++){
    int size = ntohl(*(int*)(start));
    ar[i] =new char[size]; 
    strncpy(ar[i], (char*)(start + intSize), size+1);
    start += size + intSize;
  }
}