Thread: help w/ a little naive brainstorming ...

hi all,

i've two random, not-completely-disjointed questions that have arisen in a
recent, off-the-cuff discussion.

any/all initial pointers/comments would be much appreciated ... while i slog
through Google, etc. trying to learn more ...

(1) does pgsql have/plan an equivalent of Oracle's External Tables?
    <http://www.oracle.com/technology/products/oracle9i/daily/sept19.html>

searching on 'postgresql "external tables"' i'm unclear as to whether we're
talking abt the "same fruit" here (apples & apples, ya know?).

(2) most (no, not all) email servers store message data as flat-file text,
organized in some admin-spec'd file-system directory hierarchy.

if i were to consider 'storing' those messages trees, instead, in a pgsql db, i
i mmediately/naively think of three possible options

(a) simple DB-stored references to flat file:/// locations
(b) pointers to external, actionable (parseable? writeable?) files in the FS
(c) messages stored, in their entirety as CLOBS and/or BLOBS in the DB
(d) messages converted into, say, XML, and stored in the DB
(e) message components parsed into individual fields, and stored as addressable
character and 'bytea' (a new one on me ...) data in pgsql tables

any thoughts/comments as to 'best approach' and why?

no, not at all well thought thru -- just brainstormin' .... so, flame away!  ;-)

and, thanks =)

richard

On Fri, Mar 25, 2005 at 03:16:12PM -0800, OpenMacNews wrote:

> (1) does pgsql have/plan an equivalent of Oracle's External Tables?
>    <http://www.oracle.com/technology/products/oracle9i/daily/sept19.html>

"The External Table feature allows for flat files, which reside
outside the database, to be accessed just like relational tables
within the database: the flat-file data can be queried and joined
to other tables using standard SQL."

You can implement this with a set-returning function that reads an
external source (flat file, spreadsheet, other kind of database,
etc.).  See for example DBI-link:

http://pgfoundry.org/projects/dbi-link

With views and rules you might be able to implement update/delete
operations as well, although you wouldn't get transaction semantics.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

hi michael,

>> (1) does pgsql have/plan an equivalent of Oracle's External Tables?
>>    <http://www.oracle.com/technology/products/oracle9i/daily/sept19.html>
>
> "The External Table feature allows for flat files, which reside
> outside the database, to be accessed just like relational tables
> within the database: the flat-file data can be queried and joined
> to other tables using standard SQL."
>
> You can implement this with a set-returning function that reads an
> external source (flat file, spreadsheet, other kind of database,
> etc.).  See for example DBI-link:
>
> http://pgfoundry.org/projects/dbi-link

cool.  great place to start learning ...

> With views and rules you might be able to implement update/delete
> operations as well, although you wouldn't get transaction semantics.

baby steps for me.  i'd be happy 4 now with update/delete ... and worry abt the
rest as needed later ...

thx!

richard

On Mar 25, 2005, at 6:16 PM, OpenMacNews wrote:

> if i were to consider 'storing' those messages trees, instead, in a
> pgsql db, i i mmediately/naively think of three possible options
>
> (a) simple DB-stored references to flat file:/// locations
> (b) pointers to external, actionable (parseable? writeable?) files in
> the FS
> (c) messages stored, in their entirety as CLOBS and/or BLOBS in the DB
> (d) messages converted into, say, XML, and stored in the DB
> (e) message components parsed into individual fields, and stored as
> addressable character and 'bytea' (a new one on me ...) data in pgsql
> tables
>
> any thoughts/comments as to 'best approach' and why?
>


If you were going to the trouble of using a database, I can't think of
many reasons to not just store them in regular columns with various
components parsed out for searching. There are several examples of such
systems you might want to look into:

http://www.manitou-mail.org/ (just announced)
http://yukatan.sourceforge.net/ (has links for other similar projects)

John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL