Re: COPY Transform support

Le mardi 08 avril 2008, Tom Lane a écrit :
> Dimitri Fontaine <dfontaine(at)hi-media(dot)com> writes:
> > And my main concern would still be left as-is, COPY wouldn't have any
> > facility to cope with data representation not matching what datatype
> > input functions want to read.
>
> That's sufficiently covered by the proposal to allow a COPY FROM as a
> table source within SELECT, no?

Well, yes, the table source has text as datatypes and the select expression on
the column will call whatever function/cast etc to do the work. But that
opens the door to second class citizen storage solution for PostgreSQL, by
letting the user CREATE VIEW atop of it:

CREATE VIEW csv_storage AS
SELECT a, myfunc(b)::timestamp, c::int+3
FROM (COPY ... FROM '/tmp/file.csv' ...) AS x(a, b, c)
WHERE c ~ '^[0-9]+$';

What happens to the view when /tmp/file.csv is changed (new lines appended, or
complete rewrite by another application, etc)?

Andrew comment is clear about it: he does not want PostgreSQL to offer this
kind of support. I suppose it would be possible to stop CREATE VIEW to accept
any form of SELECT, but I was hoping for my idea to get back some
attractiveness at this point :)

At least I tried ;)
--
dim