Does Pg's psql have an equivalent to Oracle's spool cmd?

[Ron Savage]

Hi Folks

If it does, I can't see it in the Ref Man # 1.

Any ideas?

--
Ron Savage
r...@savage.net.au
http://savage.net.au/index.html

[Tim Bowden]

On Tue, 2009-10-20 at 18:07 +1100, Ron Savage wrote:
> Hi Folks
>
> If it does, I can't see it in the Ref Man # 1.
>
> Any ideas?
>

copy. Note there is both an sql copy command and a psql copy command.

HTH,
Tim Bowden

[Lesley B]

On Tue, Oct 20, 2009 at 07:08:08AM -0000, dbi-users-...@perl.org wrote:
> Subject: Does Pg's psql have an equivalent to Oracle's spool cmd?
> From: Ron Savage <r...@savage.net.au>
> To: List DBI <dbi-...@perl.org>

>
> Hi Folks
>
> If it does, I can't see it in the Ref Man # 1.
>
> Any ideas?
>

Mmmm I don't know Oracle at all but
Query Buffer
\e [FILE] edit the query buffer (or file) with external editor
\ef [FUNCNAME] edit function definition with external editor
\p show the contents of the query buffer
\r reset (clear) the query buffer
\s [FILE] display history or save it to file
\w FILE write query buffer to file
Input/Output
\copy ... perform SQL COPY with data stream to the client host
\echo [STRING] write string to standard output
\i FILE execute commands from file
\o [FILE] send all query results to file or |pipe
\qecho [STRING] write string to query output stream (see \o)

might be of interest?

I used the \? command to list the above - amongst other things.

Regards

L.

[Ron Savage]

Hi Folks

On Tue, 2009-10-20 at 09:22 +0100, Lesley B wrote:
> On Tue, Oct 20, 2009 at 07:08:08AM -0000, dbi-users-...@perl.org wrote:
> > Subject: Does Pg's psql have an equivalent to Oracle's spool cmd?

$many x $thanx to those who replied.

I gather the plan is to output the SQL to a table, and then use copy to
xfer the table's data to a file...

[Peter J. Holzer]

On 2009-10-20 15:54:30 +0800, Tim Bowden wrote:
> On Tue, 2009-10-20 at 18:07 +1100, Ron Savage wrote:
> > Hi Folks

[for those who don't know the sqlplus (or dbish) spool command: It's
similar to the Unix tee(1) command: It copies all output which would
normally go onto the screen into a file.

> > If it does, I can't see it in the Ref Man # 1.
> >
> > Any ideas?
> >
>
> copy. Note there is both an sql copy command and a psql copy command.

I don't think so:

Input/Output
\copy ... perform SQL COPY with data stream to the client host

And the postgresql copy command does something very different.

\o [FILE] send all query results to file or |pipe

is more like spool, but there are two important differences:

1) it writes the results *only* to the file, not the screen.
2) it affects only the results, not the commands.

You should be able to get around the first limitation by invoking
\o |tee file
but I don't see any way around the second.

hp

--
_ | Peter J. Holzer | Auf jedem Computer sollte der Satz Ludwigs II
|_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich
| | | h...@wsr.ac.at | bleiben, mir und andern."
__/ | http://www.hjp.at/ | -- Wolfram Heinrich in desd

[Tim Bowden]

On Tue, 2009-10-20 at 11:00 +0200, Peter J. Holzer wrote:
> On 2009-10-20 15:54:30 +0800, Tim Bowden wrote:
> > On Tue, 2009-10-20 at 18:07 +1100, Ron Savage wrote:
> > > Hi Folks
>
> [for those who don't know the sqlplus (or dbish) spool command: It's
> similar to the Unix tee(1) command: It copies all output which would
> normally go onto the screen into a file.

Hmm. My understanding of spool/sqlplus just went up considerably ;-)

>
> > > If it does, I can't see it in the Ref Man # 1.
> > >
> > > Any ideas?
> > >
> >
> > copy. Note there is both an sql copy command and a psql copy command.
>
> I don't think so:
>
> Input/Output
> \copy ... perform SQL COPY with data stream to the client host
>
> And the postgresql copy command does something very different.
>
> \o [FILE] send all query results to file or |pipe
>
> is more like spool, but there are two important differences:
>
> 1) it writes the results *only* to the file, not the screen.
> 2) it affects only the results, not the commands.
>
> You should be able to get around the first limitation by invoking
> \o |tee file
> but I don't see any way around the second.
>
> hp
>

Ah yes, not quite the same at all. And interesting use of \o.

If all you want to do is get the raw data from a query into a file then
copy is a handy and simple solution (which may or may not fit the
original problem).

http://www.postgresql.org/docs/8.4/interactive/sql-copy.html

Tim Bowden

[Ron Savage]

Hi Tim

On Tue, 2009-10-20 at 17:35 +0800, Tim Bowden wrote:
> On Tue, 2009-10-20 at 11:00 +0200, Peter J. Holzer wrote:
> > On 2009-10-20 15:54:30 +0800, Tim Bowden wrote:
> > > On Tue, 2009-10-20 at 18:07 +1100, Ron Savage wrote:

[snip]

> If all you want to do is get the raw data from a query into a file then
> copy is a handy and simple solution (which may or may not fit the
> original problem).

I don't need to SQL. I am already discarding it when parsing Oracle's
output to get the column headings, their widths, and then the data.

So now with \o I'll be able to do the same with Postgres.

Thanx for the discussion.