pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: fanlijing <fanlijing@cn.fujitsu.com>
To: 'Albe Laurenz' <laurenz.albe@wien.gv.at>
To: pgsql-admin@postgresql.org
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] how to save a bytea value into a file?
Date: Mon, 10 Oct 2011 16:23:04 +0800
Message-ID: <57647D7FDC0440589824BCE3285E7155@fanlijing> (raw)
In-Reply-To: <D960CB61B694CF459DCFB4B0128514C206F740C4@exadv11.host.magwien.gv.at>
References: <038517CEB6DE43BD8422D7947B6BE8D8@fanlijing>
	<D960CB61B694CF459DCFB4B0128514C206F740C4@exadv11.host.magwien.gv.at>

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




view thread (6+ messages)  latest in thread

Message-ID: <57647D7FDC0440589824BCE3285E7155@fanlijing>
Permalink:  ../57647D7FDC0440589824BCE3285E7155@fanlijing/
Also on:    postgresql.org/message-id/57647D7FDC0440589824BCE3285E7155@fanlijing

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: fanlijing@cn.fujitsu.com, laurenz.albe@wien.gv.at, pgsql-general@postgresql.org
  Subject: Re: [GENERAL] how to save a bytea value into a file?
  In-Reply-To: <57647D7FDC0440589824BCE3285E7155@fanlijing>
  References: <038517CEB6DE43BD8422D7947B6BE8D8@fanlijing> <D960CB61B694CF459DCFB4B0128514C206F740C4@exadv11.host.magwien.gv.at> <57647D7FDC0440589824BCE3285E7155@fanlijing>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox