Hi,

I am writing a C program that accesses a 7.0.3 database using libpq.  I would like to
be able to do a fprintf through some file pointer or pipe TO the database via a PQexec call of COPY.  
I have been unable to figure out if this is possible.  I see where I can do this via stdin,
but I don't want the user to make the entry, I want the executable to do it and still enjoy
the performance of the COPY command.

I see that the COPY docs mention using a pipe instead of stdin:

"stdin       Specifies that input comes from a pipe or terminal"

However, I can find no other info regarding doing a printf through a pipe directly to the db using COPY.


Is there a way to tell the COPY command to accept a file pointer or pipe or whatever other than stdin as it's input
when copying TO the db?

Thanks!
-- 
John Coers            Intrinsity, Inc.        
coers@intrinsity.com  Austin, Texas

Wed, 04 Apr 2001 16:13:35 -0500
John Coers <coers@intrinsity.com>
COPY Question
In article <3ACB8E7F.C12A50CA@intrinsity.com>, "John Coers"
<coers@intrinsity.com> wrote:

> Hi,
> 
> I am writing a C program that accesses a 7.0.3 database using libpq.  I
> would like to be able to do a fprintf through some file pointer or pipe
> TO the database via a PQexec call of COPY. I have been unable to figure
> out if this is possible.  I see where I can do this via stdin, but I
> don't want the user to make the entry, I want the executable to do it
> and still enjoy the performance of the COPY command.

If you're on UNIX, look at the popen(3) call.

Gordon.
-- 
It doesn't get any easier, you just go faster.
   -- Greg LeMond

Wed, 04 Apr 2001 18:11:13 -0400
"Gordon A. Runkle" <gar@no-spam-integrated-dynamics.com>
Re: COPY Question
Hi,

My generic problem is performance when copying very large amounts of data to a db from multiple clients.

I am writing a C program on Linux Redhat6.2 that accesses a 7.0.3 database using libpq.  I
would like to be able to do a printf through STDOUT (or another file pointer) TO the database via a 
PQexec call of COPY.   Something like this would be ideal:

PQexec(conn, "COPY moncoverage from STDOUT");

I have been unable to figure out if this is possible.  I understand that I can do this via stdin, 
but I don't want the user to make the entry, I want the executable to do it and still enjoy the 
performance of the COPY command.

I also understand that I can open a pipe to psql and do it through there like this:

FILE *ofp = popen("psql -a -c 'COPY tablename from stdin' -d dbname -h host","w");
 
for(i=0;i<15000;i++)
   fprintf(ofp,"%s\n",row[i]);
 
fprintf(ofp,"\\.\n");
pclose(ofp);


However, performance is extremely important to me in this application.  I already have a connection
open to the db in the C executable to do 10 inserts, so it I would like to go ahead and use that
connection to perform my PQexec(conn,'COPY...') to reduce connection overhead.  Also, I am assuming that
the PQexec call makes a more efficient connection that the popen to psql and COPY scheme.  During trial
runs my server maintains 34 postmaster processes -- there has to be a better way to do this.

Any help would be greatly appreciated.  Please let me know if this is the wrong place to post this.

Mon, 09 Apr 2001 15:58:39 -0500
John Coers <coers@intrinsity.com>
libpq PQexec call of COPY
John Coers writes:

> Hi,
>
> My generic problem is performance when copying very large amounts of data to a db from multiple clients.
>
> I am writing a C program on Linux Redhat6.2 that accesses a 7.0.3 database using libpq.  I
> would like to be able to do a printf through STDOUT (or another file pointer) TO the database via a
> PQexec call of COPY.   Something like this would be ideal:
>
> PQexec(conn, "COPY moncoverage from STDOUT");
>
> I have been unable to figure out if this is possible.  I understand that I can do this via stdin,
> but I don't want the user to make the entry, I want the executable to do it and still enjoy the
> performance of the COPY command.

Read the libpq chapter in the Programmer's Guide and look into
src/bin/psql/copy.c for information and examples of using COPY through
libpq.  Yes, it's possible, but you need to use special API calls.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Tue, 10 Apr 2001 19:08:43 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: libpq PQexec call of COPY