Обсуждение: psql command for bytea output
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>. 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? cheers andrew
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>. > 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 > > > cheers > > andrew > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
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>. >> 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>. >>> 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 > > cheers > > andrew > >
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>. >>>> 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 > regards, tom lane >
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