On Mon, Jan 18, 2010 at 09:57:02AM -0500, Tom Lane wrote:
> Greg Smith <greg@2ndquadrant.com> writes:
> > Craig Ringer wrote:
> >> For those non-Oracle users among us, what's an external table?
>
> > External tables let you map a text file directly to a table without
> > explicitly loading it. In PostgreSQL, if you have data in a CSV file,
> > usually you'd import it with COPY before you'd use it. If external
> > tables were available, you'd just say there's an external table as a CSV
> > file and you could start running queries against it.
>
> I'm finding it hard to visualize a use-case for that. We must postulate
> that the table is so big that you don't want to import it, and yet you
> don't feel a need to have any index on it. Which among other things
> implies that every query will seqscan the whole table. Where's the
> savings?
I've mostly wanted something like it when I've been dealing with
externally maintained data. The best idea I've seen so far has just
been a function similar to:
copy_csv_from(filename text) returns setof text[]
to be used as:
SELECT d[0]::Int AS id, d[1] AS name
FROM copy_csv_from('/tmp/usernames.csv') d;
This could be wrapped in a VIEW giving what I'd expect to be similar
semantics to an "external table", however I've never used one so I could
be missing something.
It's possible to write this function at the moment, it's somewhat
suboptimal as the csv file is completely imported before anything else
happens so is only good for small files.
--
Sam http://samason.me.uk/