Thread: COPY FROM view
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)
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
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!
Ü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
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. +
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. +