Subject: Utility for tab_separated_text

From: [email-masked]
Date: Sun, 12 Feb 95 0:56:06 EST

Hi all,
   I am working on a project where I will have a need for a utility
that will take an arbitrary Postquel query and return the result as
tab separated text, suitable for importing into (for example) a
spreadsheet.   
   I know that the "Copy" command will copy an entire class into
a tab separated file.  However, the relations I will be working with
may grow quite large, and so it would be much more convenient to not
have to edit down the rows and columns of a complete dump.
   Before I go about writing such a thing:

1) Does anybody already have such a beast written?  

2) Or, failing that code scraps (beyond the examples in the
   std. postgres distribution) that might be helpful?

3) Or, failing that, just helpful advice for a neophyte in working with
   postgres, attempting his first libpq programming project?

Thanks for your help

--Phill Conrad					      [email-masked]
--Ph. D. Student -- Dept. of Computer and Info. Science -- Univ. of Delaware

(PS: A related utility, or perhaps a command switch? would be to write
out the results of a query to a LaTeX {tabular} environment, with column
headings=the attribute names.)

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]".
   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================
              URL: http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/

---

From: [email-masked]
Date: Mon, 13 Feb 1995 15:18:17 -0500 (EST)

Excerpts from internet.postgres: 12-Feb-95 Utility for
tab_separated_text [email-masked] (1682)

> tab separated text, suitable for importing into (for example) a

Here's a somewhat crude example (please note that I am not being very
rigorous about error checking).

#include <tmp/libpq.h>
#include <stdio.h>
#include <string.h>

int main(int argc, char *argv[])
{
    int i, j, ncols, nrows;
    char *dbname, query[256]; 
    char *retval, *pstring;
    PortalBuffer *pbuf;

    dbname = argv[0];
    sprintf(query, "retrieve (EMP.all) where EMP.name = \"bob\");

    PQsetdb(dbname);
    pstring= PQexec(query);
    pbuf = PQparray(&(pstring[1]));
    ncols = PQnfieldsGroup(pbuf, 0);
    nrows = PQntuplesGroup(pbuf, 0);

    /* Put the code for opening the file here */

    for (i = 0; i < nrows; i++) {
        char tmpbuf[4096];
        int idx;

        idx = 0;
        for (j = 0; j < ncols; j++) {
            retval = PQgetvalue(pbuf, i, j);
	    sprintf (&(tmpbuf[idx]), "%s\t", retval);
             idx += strlen(retval) + 1;
        }
        tmpbuf[idx-1] = '\n';   /* overwriting final tab */

/* Put code for writing tmpbuf to the file */  
    }
/* Close the file */
    PQfinish();
    return(0);
}

I may have overlooked something but you should get the general idea of
how to do it.

Robert