Thread: COPY FROM view

COPY FROM view

From
Alvaro Herrera
Date:
Hi,

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?

-- 
Alvaro Herrera                                http://www.PlanetPostgreSQL.org
"No necesitamos banderasNo reconocemos fronteras"                  (Jorge González)


Re: COPY FROM view

From
Tom Lane
Date:
Alvaro Herrera <alvherre@alvh.no-ip.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 syntaxCOPY (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


Re: COPY FROM view

From
David Fetter
Date:
On Sun, May 28, 2006 at 03:59:44PM -0400, Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.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 ...

+1 :)

Cheers,
D (who now goes with CREATE TEMP TABLE, COPY, DROP)
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: COPY FROM view

From
Hannu Krosing
Date:
Ühel kenal päeval, P, 2006-05-28 kell 13:53, kirjutas Alvaro Herrera:
> Hi,
> 
> I've been having the COPY FROM patch that was posted on pgsql-patches
> some time ago (I think from Hannu Krossing), 

Not by/from me :)


-- 
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com





Re: COPY FROM view

From
Bruce Momjian
Date:
Hannu Krosing wrote:
> ?hel kenal p?eval, P, 2006-05-28 kell 13:53, kirjutas Alvaro Herrera:
> > Hi,
> > 
> > I've been having the COPY FROM patch that was posted on pgsql-patches
> > some time ago (I think from Hannu Krossing), 
> 
> Not by/from me :)

Patch was from Karel Zak:
http://candle.pha.pa.us/mhonarc/patches_hold/msg00118.html

--  Bruce Momjian   http://candle.pha.pa.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: COPY FROM view

From
Bruce Momjian
Date:
So we don't want COPY FROM VIEW in 8.2?  Even if we later support COPY
(SELECT ...), aren't we still going to want to copy from a view?  I
guess not because you would just do COPY (SELECT * FROM view)?

---------------------------------------------------------------------------

Tom Lane wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +