Re: [GENERAL] help w/ a little naive brainstorming ...

[John DeSoi]

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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] help w/ a little naive brainstorming ...

[OpenMacNews]

hi michael,
(1) does pgsql have/plan an equivalent of Oracle's External Tables?
   
"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
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq

Re: [GENERAL] help w/ a little naive brainstorming ...

[Michael Fuhr]

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

> (1) does pgsql have/plan an equivalent of Oracle's External Tables?
>

"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/

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[GENERAL] help w/ a little naive brainstorming ...

[OpenMacNews]

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?
   
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
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match