Re: Searching BLOB

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: James Watson <jdwatson1(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Searching BLOB
Date: 2006-06-13 12:17:19
Message-ID: 448EACCF.9000800@phlo.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Florian G. Pflug 2006-06-13 12:22:39 Re: pg_dump: missing pg_database entry
Previous Message John Smith 2006-06-13 08:57:32 Re: ECPG and Curors.