Обсуждение: how to save a bytea value into a file?

Поиск
Список
Период
Сортировка

how to save a bytea value into a file?

От
"fanlijing"
Дата:
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
--------------------------------------------------------


Re: how to save a bytea value into a file?

От
"Albe Laurenz"
Дата:
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

Re: how to save a bytea value into a file?

От
"Albe Laurenz"
Дата:
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

Re: how to save a bytea value into a file?

От
"fanlijing"
Дата:
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


Re: how to save a bytea value into a file?

От
"Albe Laurenz"
Дата:
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

Re: how to save a bytea value into a file?

От
Merlin Moncure
Дата:
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