Re: COPY FROM view

Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
> I've been having the COPY FROM patch that was posted on pgsql-patches
> some time ago (I think from Hannu Krossing), sitting on my machine, with
> the intention to commit it for 8.2. However there's something I'm not
> very sure about -- the patch creates an execution plan by passing a
> literal "SELECT * FROM view" to pg_parse_query, pg_analyze_and_rewrite,
> and finally planner().

> I'm sure we can make this work appropiately, patching over the quoting
> issues that the patch doesn't deal with, but I'm unsure if this is an
> acceptable approach. (Actually I think it isn't.) But what is an
> acceptable way to do it?

It seems to me that we had decided that "COPY FROM VIEW" is not even the
conceptually right way to think about the missing feature. It forces
you to create a view (at least a temporary one) in order to do what you
want. Furthermore it brings up the question of why can't you COPY TO
VIEW. The correct way to think about it is to have a way of dumping the
output of any arbitrary SELECT statement in COPY-like format.

There was some previous discussion of exactly how to go about that;
check the archives. Offhand I think we might have liked the syntax
COPY (parenthesized-SELECT-statement) TO ...
but there was also some argument in favor of using a separate statement
that basically "sets the output mode" for a subsequent SELECT. I'm
not sure if anyone thought about how it would play with psql's \copy
support, but that's obviously something to consider.

regards, tom lane