[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2436
  • Last Modified:

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
0
miyahira
Asked:
miyahira
  • 3
  • 2
1 Solution
 
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
 
miyahiraAuthor 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
 
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now