[ADMIN] [GENERAL] how to save a bytea value into a file?

[Albe Laurenz]

When I want to save a bytea value into a file, what should I do$B!)(J
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

[Albe Laurenz]

In Fact, I'm doing a porting project from Oracle 10g to PostgreSQL 9.0.4

[...]

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)
$B!z(J 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

[Albe Laurenz]

I wrote:
[fanlijing wants to write bytea to file]

Post by Albe Laurenz
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

[Merlin Moncure]

Post by Albe Laurenz
[fanlijing wants to write bytea to file]

Post by Albe Laurenz
A simple
   COPY (SELECT byteacol WROM mytab WHERE ...) TO 'filename' (FORMAT

binary)

Post by Albe Laurenz
should do the trick.

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