Skip site navigation (1) Skip section navigation (2)

Re: COPY TO Question?

From: John DeSoi <desoi(at)pgedit(dot)com>
To: Marc Abbott <MarcA(at)Medscheme(dot)co(dot)za>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: COPY TO Question?
Date: 2008-08-06 13:47:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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.

In response to

pgsql-novice by date

Next:From: Wm.A.StaffordDate: 2008-08-06 18:58:49
Subject: A couple of basic questions
Previous:From: Daniel GourDate: 2008-08-06 12:16:07
Subject: Re: How to copy a schema under another name in same database

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group