2016-12-06 1:50 GMT+01:00 Kohei KaiGai <kai...@kaigai.gr.jp>: > 2016-12-05 22:45 GMT+09:00 Pavel Stehule <pavel.steh...@gmail.com>: > > > > There are more goals: > > > > 1. user friendly import of text or binary data - import text data (with > > psql) from file is possible - but you have to load a content to psql > > variable. For binary data you should to use workaround based on LO and > > transformation from LO to bytea. > > > > 2. user friendly export text or binary data - now, the binary data can be > > exported only via transformation to LO. The XML has very interesting > > features when is passing from/to client binary. This feature is > impossible > > in psql now. > > > : > <snip> > : > >> It seems to me extend of COPY statement for this optimization is a bit > >> overkill > >> solution. Do we find out an alternative solution that we can build on > >> the existing > >> infrastructure? > > > > The advantage and sense of COPY RAW was reusing existing interface. The > > question was: How I can export/import binary data simply from psql > console? > > > OK, I could get your point. > > Likeky, we can implement the feature without COPY statement enhancement > by adding a special purpose function and \xxx command on psql. > > Let's assume the two commands below on psql: > > \blob_import <table_name> <column_name> (STDIN|<filename>) > \blob_export <query> (STDOUT|<filename>) > > On \blob_import, the psql command reads the binary contents from either > stdin or file, than call a special purpose function that takes three > arguments; table name, column name and a binary data chunk. > PQexecParams() of libpq allows to deliver the data chunk with keeping > binary data format, then the special purpose function will be able to > lookup the destination table/column and construct a tuple that contains > the supplied data chunk. (I think xxxx_recv handler shall be used for > data validation, but not an element of this feature.) > > > On \blob_export, the psql command also set up a simple query as follows: > SELECT blob_export((<user's supplied query)) > For example, > \blob_export SELECT binary_data FROM my_table WHERE id = 10 /tmp/aaa > shall be transformed to > SELECT blob_export((SELECT binary_data FROM my_table WHERE id = 10)) >
This is reason why I prefer a COPY statement - because it does all necessary things natural. But if there is a disagreement against COPY RAW it can be implemented as psql commands. export should be similar like \g, \gset feature so SELECT xmldoc FROM xxxx \gbinary_store xxxx.xxx import is maybe better solved by proposed file references in queries Regards Pavel > > This function is declared as: > blob_export(anyelement) RETURNS bytea > So, as long as the user supplied query returns exactly one column and > one row, it can transform the argument to the binary stream, then psql > command receive it and dump somewhere; stdout or file. > > How about your thought? > > Thanks, > -- > KaiGai Kohei <kai...@kaigai.gr.jp> >
