Postgres support for grg

Hi all,
For those interested in the GNU Report Generator GRG,
I have extended this
utility to support the Postgres SQL database.

To get this to work, you first need to get the
latest GRG code ( e.g., The current release of GRG can be got from:

  ftp://ftp.dai.ed.ac.uk/pub/daidb/grg/grg-1.43.tar.gz

or goto the home page at:

  http://www.dai.ed.ac.uk/daidb/grg/
)

I have included below the postgres.c file which
does the interfacing. Unfortunately I was unable to
get the configure script to correctly work
with the new postgres file.

To get this to work, postgres.c must be included
in src/sql.c, the postgres include directory must
also be specified in the compile line, and
libpq.a must also be linked in.

Have fun,
Cheers
Anthony

--
--------------------------------------------------------
Dr Anthony Symons       | Phone: +61-73-259-2365
CiTR Pty Ltd            | Fax:   +61-73-259-2259
John Oxley Centre       |
339 Coronation Drive    | Email: a.symons@citr.com.au
MILTON                  |
QLD 4064 AUSTRALIA      | Postal: PO Box 1643
--------------------------------------------------------



/****************************************************************************/

/* @(#)ingres.sc    1.4 Copyright (C) University of Edinburgh, 12 Nov 1997.
 *
 * GRG - GNU Report Generator
 *
 * Copyright (C) University of Edinburgh, 1993-1997. All rights reserved.
 * The Author, Tim Edward Colles, has exercised his right to be identified
 * as such under the Copyright, Designs and Patents Act 1988.
 *
 * This program is free software; you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation; either version 2, or (at your option)
 * any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
 * 02111-1307, USA.
 */

/*
 * GRG INGRES HANDLER
 */


#include <stdio.h>
/*#include <postgres.h>*/
#include <libpq-fe.h>
/*#include <catalog/pg_type.h>*/
#include <ctype.h>
#define BOOLOID                 16
#define BYTEAOID                17
#define CHAROID 18
#define NAMEOID 19
#define INT2OID                 21
#define INT28OID                22
#define INT4OID                 23
#define TEXTOID 25
#define OIDOID                  26
#define FLOAT4OID 700
#define FLOAT8OID 701
#define UNKNOWNOID              705
#define CASHOID 790
#define BPCHAROID               1042
#define VARCHAROID              1043
#define DATEOID                 1082
#define TIMEOID                 1083
#define DATETIMEOID             1184
#define TIMESPANOID             1186
#define TIMESTAMPOID    1296

/*
 * Run variable 'select' statement on an Ingres RDBMS and load as database
 */

int
gppSQLOpen(nm,qry,da) char *nm, *qry; dbase **da;
{
    int i,  j, ptr = 0;
    int tuple;

    dbase *db;
    char fname[32];
    char dbname[51];
    char stmt_buffer[4096];
    PGresult * qry_res = NULL;
    PGconn * connection = NULL;

    strncpy(dbname,texphysdb,50);
    if (!strcmp(qry,"")) {
        strcpy(qry,"SELECT * FROM ");
        strcat(qry,nm);
    }
    strcpy(stmt_buffer,qry);

    /* make connection */
    if ( NULL == (connection = PQsetdb(NULL, /* host*/
                                       NULL, /* pg port */
                                       NULL, /* options */
                                       NULL, /* pgtty */
                                       dbname)
                  ))
    {
        /* did not connect */
        fprintf(stderr,"%s:%d: gppSQLOpen could not make connection to %s \n",
                __FILE__,__LINE__,
                dbname);
        return(db_FOP);
    }
    if (CONNECTION_BAD == PQstatus(connection))
    {
        fprintf(stderr,"%s:%d: gppSQLOpen could not make connection %s\n",
                __FILE__,__LINE__,
                PQerrorMessage(connection));
        return(db_FOP);
    }

    /* now execute the sql query */
    if (NULL == (qry_res = PQexec(connection, stmt_buffer)))
    {
        fprintf(stderr,"%s:%d: gppSQLOpen got null result %s \n",
                __FILE__,__LINE__,
                PQerrorMessage(connection));
        PQfinish(connection);

        return(db_FOP);
    }
    /* was the query ok? */
    if (!( PGRES_COMMAND_OK == PQresultStatus(qry_res)
           || PGRES_TUPLES_OK == PQresultStatus(qry_res))
        )
    {
        fprintf(stderr,"%s:%d: gppSQLOpen  query failed %s\n",
                __FILE__,__LINE__,
                PQerrorMessage(connection));
        PQclear(qry_res);
        PQfinish(connection);

        return(db_FOP);
    }




    if (!(db = (dbase *) calloc(1,sizeof(dbase)))) return(db_MEM);
    *da = db;
    db->dname = nm;
    db->dfile = NULL;
    db->dnfld = PQnfields(qry_res);
    db->dnrec = 1;
    db->dtrec = 0;
    if (!(db->dhead = (db_head *) calloc(1,sizeof(db_head)))) return(db_MEM);
    db->dhead->db_nrecs = db->dtrec;
    if (!(db->dfrec = (db_field *) calloc(db->dnfld,sizeof(db_field))))
        return(db_MEM);
    for (i = 0; i <  PQnfields(qry_res); i++)
    {
        char * qry_fname = PQfname(qry_res,i);

        if (texnamcol)
        {
            for (j = 0; j < strlen(qry_fname) && j < 10; j++)
            {
                db->dfrec[i].fl_fname[j] = toupper(qry_fname[j]);
            }
            db->dfrec[i].fl_fname[j] = '\0';
        }
        else
        {
            sprintf(fname,"U%03d",i+1);
            strcpy(db->dfrec[i].fl_fname,fname);
        }
        switch(PQftype(qry_res, i))
        {
        case INT2OID:
        case INT4OID:
        case INT28OID:
        case BOOLOID:
        case OIDOID:
            /* int types */
            db->dfrec[i].fl_fsize = 12;
            db->dfrec[i].fl_ftype = 'N';
            break;



        case CASHOID:
        case FLOAT4OID:
        case FLOAT8OID:
            /* float types */
            db->dfrec[i].fl_fsize = 14;
            db->dfrec[i].fl_ftype = 'N';
            break;

        case BYTEAOID:
        case CHAROID:
        case NAMEOID:
        case TEXTOID:
        case VARCHAROID:
        case DATEOID:
        case TIMEOID:
        case DATETIMEOID:
        case TIMESPANOID:
        case TIMESTAMPOID:


            db->dfrec[i].fl_fsize = PQfsize(qry_res, i);
            db->dfrec[i].fl_ftype = 'C';
            break;
        default:
            db->dfrec[i].fl_fsize = PQfsize(qry_res, i);
            db->dfrec[i].fl_ftype = 'C';
            break;
        }

    }
    /* now check if any had a size -1 */
    for (i = 0; i < db->dnfld; i++)
        if (db->dfrec[i].fl_fsize < 0)
        {
            /* find the maximum field size */
            int k,  mx = 1 /* some default? */, tmp;
            for(k = 0; k < PQntuples(qry_res); k++)
            {

                tmp = PQgetlength(qry_res,k,i);
                if (tmp > mx)
                    mx = tmp;
            }
            db->dfrec[i].fl_fsize = mx;
        }




    for (i = 0; i < db->dnfld; i++)
        db->dnrec += db->dfrec[i].fl_fsize;
    if (!(db->drrec = (char *) realloc(NULL,1)))
        return(db_MEM);
    for (tuple = 0; tuple < PQntuples(qry_res); tuple ++)
    {

        db->dtrec++;
        if (!(db->drrec = (char *) realloc(db->drrec,db->dtrec*db->dnrec+1)))
            return(db_MEM);
        for (i = 0; i < PQnfields(qry_res); i++)
        {
            char * qry_data = PQgetvalue(qry_res,tuple,i);


            if (NULL == qry_data)
            {
                sprintf(&db->drrec[ptr],"%-.*s",db->dfrec[i].fl_fsize,texnull);
            }
            else
            {

                sprintf(&db->drrec[ptr],"%*s",db->dfrec[i].fl_fsize,
                        qry_data);

            }
            ptr += db->dfrec[i].fl_fsize;
        }
        db->drrec[ptr++] = ' ';
    }

    db->drrec[ptr-1] = 0x1A;
    db->dhead->db_nrecs = db->dtrec;
    PQclear(qry_res);
    PQfinish(connection);

    return(0);
}

/****************************************************************************/