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

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