Re: Searching BLOB

James Watson wrote:
> What I was hoping someone could help me out with was identifying the
> best possible solution to use.
>
> 1. How can I store the word doc's in the DB, would it be best to use a
> BLOB data type?
You can use the column type "bytea", which can store (nearly) arbitrary
amounts of binary data.

> 2. Does Postgres support full text searching of a word document once it
> is loaded into the BLOB column & how would this work?   Would I have to
> unload each BLOB object, convert it back to text to search, or does
> Postgres have the ability to complete the full-text search of a BLOB,
> like MSSQL Server & Oracle do?
There is fulltext indexing support for postgres, look for tsearch2 in
the contrib module of postgres. A bytea-column is basically used like
a string, so there is no need to load/unload the blob.

There is also the concept of a LOB as a distinct entity in postgresql.
Accessing those lobs needs special support from your client library
(standard libpq provides that support of course). They have the advantage
that you can open/seek/close them like a regular file. But the disadvantage
is that you can't store them in columns - they are referenced via oids, and
you need to store those oids. You also can't put triggers on those LOBs, and
I'm not sure how transaction-safe they are.

> 3. Is there a way to export the Word Doc From the BLOB colum and dump
> it into a PDF format (I guess I am asking if someone has seen or
> written a PDF generator script/storedProc for Postgres)?
You can use java as a backend language with postgresql (google for pljava).
So you can pretty much do whatever you can do with java.

greetings, Florian Pflug