How to export a blob field to Windows folder?

Hello ,

My postgres table has a blob field that stores MS-Word files. I need to loop the table and export each blob field as a Word document to a Windows folder.

I'm using pgAdmin, and trying to do this:

SELECT lo_export(bl_mydocument, 'C:\x\MyDocument.doc')
  FROM MyTable
  where bl_field='514-2009';

Got this error:

ERROR:  function lo_export(bytea, "unknown") does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

********** Error **********

ERROR: function lo_export(bytea, "unknown") does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may need to add explicit type casts.

My Postgres database has this software version:
- PostgreSQL 7.4.2
- O/S: Conectiva Linux 10

Client:
pgAdmin Version 1.10.5, in Windows XP
LVL 1
miyahiraAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
miyahiraConnect With a Mentor Author Commented:
Thanks for the response. You are right, field bl_mydocument is bytea type, not oid type. Actually, I realized that lo_export function does not work for bytea types. At the end, I had to make a program with .net to extract all blobs.

I think that postgres has no function to export data from bytea types. Am I right?
0
 
miyahiraAuthor Commented:
Also, I'm trying to run that function using OID parameter:

SELECT lo_export(1202474, 'C:\x\MyDocument.doc')
  FROM MyTable
  where bl_field='514-2009';

but got error:


ERROR:  large object 1202474 does not exist

********** Error **********

ERROR: large object 1202474 does not exist
SQL state: 42704
0
 
earth man2Commented:
SELECT bl_mydocument FROM MyTable where bl_field='514-2009';
SELECT bl_mydocument::oid FROM MyTable where bl_field='514-2009';
SELECT lo_export(bl_mydocument::oid, 'C:\x\MyDocument.doc') FROM MyTable where bl_field='514-2009';
SELECT lo_export(1202474:oid, 'C:\x\MyDocument.doc') ;

MyTable.bl_mydocument has probably been defined as bytea type where it is probably better off being an oid type.
0
 
earth man2Commented:
The author took my hints and arrived at a solution, then asks a rhetorical question....
0
 
miyahiraAuthor Commented:
Didn't get response after my last comment.
0
All Courses

From novice to tech pro — start learning today.