Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 90DE3B5DBD0 for ; Mon, 10 Oct 2011 05:23:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 90194-03; Mon, 10 Oct 2011 08:23:49 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from song.cn.fujitsu.com (unknown [222.73.24.84]) by mail.postgresql.org (Postfix) with ESMTP id 3DFFFB5DBCA; Mon, 10 Oct 2011 05:23:48 -0300 (ADT) Received: from tang.cn.fujitsu.com (tang.cn.fujitsu.com [10.167.250.3]) by song.cn.fujitsu.com (Postfix) with ESMTP id 92C58170138; Mon, 10 Oct 2011 16:23:40 +0800 (CST) Received: from jfttmail.jftt.cn.fujitsu.com (tang.cn.fujitsu.com [127.0.0.1]) by tang.cn.fujitsu.com (8.14.3/8.13.1) with ESMTP id p9A8NZKh023023; Mon, 10 Oct 2011 16:23:39 +0800 Received: from fanlijing (unknown [10.167.14.111]) by jfttmail.jftt.cn.fujitsu.com (Postfix) with ESMTP id 82B187F72; Mon, 10 Oct 2011 16:25:18 +0800 (CST) X-SecurityPolicyCheck-FJ: OK by FujitsuOutboundMailChecker v1.4.0 Received: from fanlijing[10.167.14.111] by fanlijing (FujitsuOutboundMailChecker v1.4.0/9992[10.167.14.111]); Mon, 10 Oct 2011 16:23:14 +0800 (JST) From: "fanlijing" To: "'Albe Laurenz'" , , References: <038517CEB6DE43BD8422D7947B6BE8D8@fanlijing> 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> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-2022-jp" Content-Transfer-Encoding: 7bit X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcyGWZEyayt1FUq5R0q6Cy1384894wAw3tOgAABa7SA= In-Reply-To: X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6109 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.107 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201110/68 X-Sequence-Number: 38520 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