[HACKERS] psql command for bytea output

2011-10-21 Thread Andrew Dunstan


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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Pavel Stehule
2011/10/21 Andrew Dunstan andrew.duns...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Andrew Dunstan

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

2011/10/21 Andrew Dunstanandrew.duns...@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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Pavel Stehule
2011/10/21 Andrew Dunstan andrew.duns...@pgexperts.com:
 On 10/21/2011 02:44 PM, Pavel Stehule wrote:

 2011/10/21 Andrew Dunstanandrew.duns...@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



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Andrew Dunstan

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

2011/10/21 Andrew Dunstanandrew.duns...@pgexperts.com:

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

2011/10/21 Andrew Dunstanandrew.duns...@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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes:
 2011/10/21 Andrew Dunstan andrew.duns...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Pavel Stehule
2011/10/21 Tom Lane t...@sss.pgh.pa.us:
 Pavel Stehule pavel.steh...@gmail.com writes:
 2011/10/21 Andrew Dunstan andrew.duns...@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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Andrew Dunstan

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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] psql command for bytea output

2011-10-21 Thread Tom Lane
Andrew Dunstan andrew.duns...@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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers