Обсуждение: how to save a bytea value into a file?
Hello PostgreSQL members： I'm a user of PostgreSQL. Now I have a question when using it. When I want to save a bytea value into a file, what should I do？ Is there any function dealing with that in PostgreSQL? (like lo_export() to deal with the large-object) (# I didn't find any....) Sorry for interrupting you. Thanks anyway. Best regards Fan Lijing 2011-10-09 Regards Fan Lijing -------------------------------------------------------- Oversea Business Department, Jiangsu Fujitsu Telecommunications Technology Co.,Ltd.(JFTT) No.118, Dengwei Rd.,Suzhou, P.R. China Postcode: 215011 PHONE #86-512-68250097-8506 / COINS : 7991-8506 FAX #86-512-68250045 / COINS FAX : Mail #fanlijing@cn.fujitsu.com --------------------------------------------------------
fanlijing wrote: > When I want to save a bytea value into a file, what should I do？ > Is there any function dealing with that in PostgreSQL? (like lo_export() to > deal with the large-object) (# I didn't find any....) If you want to save it in a file on the server, you can use the COPY statement. If you want to create that file on the client machine, you'll have to select and fetch it like any other value, then open a file and write the contents into it. If the bytea is large, you could select parts of it (with the substring() function) and thus do it in pieces. For some APIs, you could also use COPY TO STDOUT. Details depend on the API and programming language you are using. Yours, Laurenz Albe
fanlijing wrote: > In Fact, I'm doing a porting project from Oracle 10g to PostgreSQL 9.0.4 > > There is a procedure in Oracle 10g to write a blob value into a file using: [...] > I know PostgreSQL doesn't support procedure, so I want to porting it into a > function use LANGUAGE plpgsql. > So I must find some APIs supported by plpgsql to write a bytea value into a > file (e.g. write into a file in RedHat) > > ★ Are there any APIs like "UTL_FILE.FOPEN... DBMS_LOB.READ... > UTL_FILE.PUT_RAW..." of Oracle dealing with writing binary objects into a > file in PostgreSQL? If there isn't anyone, Maybe I should think about other > language(e.g. Perl, Java) to realize it. There are functions to read files: http://www.postgresql.org/docs/current/static/functions-admin.html#FUNCTIONS-ADMIN-GENFILE The Contrib-Module "adminpack" contains a function "pg_file_write" that can be used to write files. But you don't need those for what you want to do. A simple COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) should do the trick. For all that you need superuser privileges. Yours, Laurenz Albe
Hello Laurenz Albe
Thank you for your replying.
In Fact, I'm doing a porting project from Oracle 10g to PostgreSQL 9.0.4
There is a procedure in Oracle 10g to write a blob value into a file using:
...
V_TOTAL_SIZE := DBMS_LOB.GETLENGTH(V_BLOB);
V_FILE_TYPE := UTL_FILE.FOPEN('RADIUS_DIR', V_FILE_NAME, V_OPEN_MODE,
V_BUFFER_SIZE);
WHILE V_OFFSET < V_TOTAL_SIZE LOOP
IF V_TOTAL_SIZE < V_OFFSET + V_AMOUNT THEN
V_AMOUNT := V_TOTAL_SIZE - V_OFFSET + 1;
END IF;
DBMS_LOB.READ(V_BLOB, V_AMOUNT, V_OFFSET, V_BUFFER);
UTL_FILE.PUT_RAW(V_FILE_TYPE, V_BUFFER, TRUE);
V_OFFSET := V_OFFSET + V_AMOUNT;
DBMS_OUTPUT.PUT_LINE ('Offset : ' || V_OFFSET);
END LOOP;
UTL_FILE.FCLOSE(V_FILE_TYPE);
...
I know PostgreSQL doesn't support procedure, so I want to porting it into a
function use LANGUAGE plpgsql.
So I must find some APIs supported by plpgsql to write a bytea value into a
file (e.g. write into a file in RedHat)
★ Are there any APIs like "UTL_FILE.FOPEN... DBMS_LOB.READ...
UTL_FILE.PUT_RAW..." of Oracle dealing with writing binary objects into a
file in PostgreSQL? If there isn't anyone, Maybe I should think about other
language(e.g. Perl, Java) to realize it.
Waiting for your reply^^~
Best Regards
Fanlijing
2011-10-10
-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: Monday, October 10, 2011 3:28 PM
To: fanlijing *EXTERN*; pgsql-admin@postgresql.org;
pgsql-general@postgresql.org
Subject: RE: [GENERAL] how to save a bytea value into a file?
fanlijing wrote:
> When I want to save a bytea value into a file, what should I do？
> Is there any function dealing with that in PostgreSQL? (like lo_export()
to
> deal with the large-object) (# I didn't find any....)
If you want to save it in a file on the server, you can use
the COPY statement.
If you want to create that file on the client machine, you'll
have to select and fetch it like any other value, then open
a file and write the contents into it.
If the bytea is large, you could select parts of it (with the
substring() function) and thus do it in pieces.
For some APIs, you could also use COPY TO STDOUT.
Details depend on the API and programming language you are using.
Yours,
Laurenz Albe
I wrote: [fanlijing wants to write bytea to file] > A simple > COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT binary) > should do the trick. Corrections: a) "binary" must be surrounded by single quotes. b) that won't dump just the binary data - you would have to remove the first 25 bytes and the last 2 bytes... So maybe using the functions I mentioned would be the best way after all. You could also write your own user defined function in C. Yours, Laurenz Albe
On Mon, Oct 10, 2011 at 9:26 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote: > I wrote: > [fanlijing wants to write bytea to file] >> A simple >> COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT > binary) >> should do the trick. > > Corrections: > a) "binary" must be surrounded by single quotes. > b) that won't dump just the binary data - you would have > to remove the first 25 bytes and the last 2 bytes... > > So maybe using the functions I mentioned would be the > best way after all. > > You could also write your own user defined function in C. A user defined function, or a binary wire format speaking client side extractor in C/libpq. The basic rule of thumb for me regarding C inside the server is 'only when no other reasonable solution exists'. merlin