Re: COPY TO Question?

On Aug 6, 2008, at 7:59 AM, Marc Abbott wrote:

> I am currently running PostgreSQL 8.1.3 on i686-pc-linux-gnu,
> compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-47).
> What I need to do is COPY FROM a file into a temp table (This I know
> how to do), then select and manipulate the data and write it back
> out to groups of files. I can insert the manipulated data into
> another table and then use COPY TO a file but what I would like to
> do is open a file with a name and write pipe delimited lines to it
> and close it and reopen the next file, write and so on ….. pretty
> similar to Oracle’s utl_file.fopen, utl_file.fclose,
> utl_file.put_line etc. Is this available in postgres?

I'm not sure if this is implemented in 8.1, but recent versions of
PostgreSQL support COPY for general SQL expressions so you don't need
to create more tables.

For example:

COPY (select * from my_table) TO 'path/to/file';

I don't think there are any other file manipulation capabilities in
PostgreSQL without using an untrusted procedural language.

>
> Secondly, is it possible to FTP the same files to a different
> server? Would I need to write some form of shell script and execute
> it? If so how would I go about doing this from the DB?
>

You would need to use one of the untrusted procedural languages in
order to do this directly from the database.

John DeSoi, Ph.D.