psql command for bytea output

2011/10/21 Andrew Dunstan <andrew.dunstan@pgexperts.com>:

A few months ago, I blogged about the difficulty of getting psql to put a
bytea datum into a file. See
<http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html&gt;.
Today I ran into the problem again, and it struck me that we could fairly
easily have a new command to handle this, called, say, bcopy. So it would
look like:

  \bcopy (select generate_bytea()) to foo.bin

Thoughts?

isn't better to fix current tools to work well with bytea?

Pavel

On 10/21/2011 02:44 PM, Pavel Stehule wrote:

2011/10/21 Andrew Dunstan<andrew.dunstan@pgexperts.com>:

A few months ago, I blogged about the difficulty of getting psql to put a
bytea datum into a file. See
<http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html&gt;.
Today I ran into the problem again, and it struck me that we could fairly
easily have a new command to handle this, called, say, bcopy. So it would
look like:

\bcopy (select generate_bytea()) to foo.bin

Thoughts?

isn't better to fix current tools to work well with bytea?

Such as?

cheers

andrew

2011/10/21 Andrew Dunstan <andrew.dunstan@pgexperts.com>:

On 10/21/2011 02:44 PM, Pavel Stehule wrote:

2011/10/21 Andrew Dunstan<andrew.dunstan@pgexperts.com>:

A few months ago, I blogged about the difficulty of getting psql to put a
bytea datum into a file. See

<http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html&gt;.
Today I ran into the problem again, and it struck me that we could fairly
easily have a new command to handle this, called, say, bcopy. So it would
look like:

  \bcopy (select generate_bytea()) to foo.bin

Thoughts?

isn't better to fix current tools to work well with bytea?

Such as?

some like

\copy ... to foo.bin format binary

a COPY API can do it - it support a binary load and binary dump, so
just there is missing interface

Pavel

On 10/21/2011 02:51 PM, Pavel Stehule wrote:

2011/10/21 Andrew Dunstan<andrew.dunstan@pgexperts.com>:

On 10/21/2011 02:44 PM, Pavel Stehule wrote:

2011/10/21 Andrew Dunstan<andrew.dunstan@pgexperts.com>:

A few months ago, I blogged about the difficulty of getting psql to put a
bytea datum into a file. See

<http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html&gt;.
Today I ran into the problem again, and it struck me that we could fairly
easily have a new command to handle this, called, say, bcopy. So it would
look like:

\bcopy (select generate_bytea()) to foo.bin

Thoughts?

isn't better to fix current tools to work well with bytea?

Such as?

some like

\copy ... to foo.bin format binary

a COPY API can do it - it support a binary load and binary dump, so
just there is missing interface

That would be fine if you could suppress the file header/trailer and
field header, so all you got was the raw data. But making COPY do that
seems no cleaner than what I suggested.

cheers

andrew

Pavel Stehule <pavel.stehule@gmail.com> writes:

2011/10/21 Andrew Dunstan <andrew.dunstan@pgexperts.com>:

On 10/21/2011 02:44 PM, Pavel Stehule wrote:

isn't better to fix current tools to work well with bytea?

Such as?

some like
\copy ... to foo.bin format binary

No, because COPY BINARY will emit its own sort of wrappers around the
data.

What I don't like about Andrew's proposal is that it seems rather
limited. Why bytea in particular? Text chunks could probably also use
a direct output method. And what about input?

Could we do anything with a notion of a COPY RAW mode, that would dump
or read the data without any header, column, or row separators? On
input I suppose you'd have to restrict it to one column --- on output,
we could leave re-dividing the data to the user's ingenuity ...

regards, tom lane

2011/10/21 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2011/10/21 Andrew Dunstan <andrew.dunstan@pgexperts.com>:

On 10/21/2011 02:44 PM, Pavel Stehule wrote:

isn't better to fix current tools to work well with bytea?

Such as?

some like
\copy ... to foo.bin format binary

No, because COPY BINARY will emit its own sort of wrappers around the
data.

true - it's not useful for this case

What I don't like about Andrew's proposal is that it seems rather
limited.  Why bytea in particular?  Text chunks could probably also use
a direct output method.  And what about input?

Could we do anything with a notion of a COPY RAW mode, that would dump
or read the data without any header, column, or row separators?  On
input I suppose you'd have to restrict it to one column --- on output,
we could leave re-dividing the data to the user's ingenuity ...

+1

Andrew - has sense a input/output different than 1row/1column?

Regards

Pavel

On 10/21/2011 04:43 PM, Pavel Stehule wrote:

What I don't like about Andrew's proposal is that it seems rather
limited. Why bytea in particular? Text chunks could probably also use
a direct output method. And what about input?

Could we do anything with a notion of a COPY RAW mode, that would dump
or read the data without any header, column, or row separators? On
input I suppose you'd have to restrict it to one column --- on output,
we could leave re-dividing the data to the user's ingenuity ...

+1

Andrew - has sense a input/output different than 1row/1column?

Well, my main use case is only for output of a single datum. Sure we
could run them all together on output, but it's hard to see what use
that would be unless everything were of fixed size.

Being able to load a single datum would certainly be good, and not just
for bytea fields - for any large piece of text as well, in fact. One use
case I have for that involves a number of fairly large XSL stylesheets
that need to be loaded into a database. Currently that's done via psql
variables, but it's a bit grotty.

I also think this is really just a psql issue. Most other clients (e.g.
a perl DBD::Pg client) will have no trouble handling all this now.

cheers

andrew

Andrew Dunstan <andrew.dunstan@pgexperts.com> writes:

I also think this is really just a psql issue. Most other clients (e.g.
a perl DBD::Pg client) will have no trouble handling all this now.

Well, that's debatable, and in any case I think you'd find it will be
easier to push this into COPY than to invent new psql backslash commands
for it.

regards, tom lane