Thread: feature proposal ...
hackers,
currently we have to hack tons of export scripts for various customers.
the problem is: if tables can be exported straight forward COPY will
give you all you need but when data has to be transformed while
exporting things start becoming a bit more complex. usually people want
to have CSV file (excel-ify data) which is supported by COPY.
the problem is: COPY can write data returned by a SELECT statement to a
file. our idea is to implement precisely that.
example:
COPY TO file_name USING some_select_statement;
the advantage would be that COPY would then be able to export data and
transform it on the fly. this would save many people a lot of work
because complex data extractors could in many cases be replaced by
simple SQL scripts.
how we plan to implement that:
currently copy simply opens a table and loops through the tuples (see
command/copy.c starting at line 1115).
to implement the desired feature we just had to add some SPI code to the
scenery (SPI will also return HeapTuples so it should fit in there).
Any comments?
Best regards,
Hans
--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
> the problem is: COPY can write data returned by a SELECT statement to a
> file. our idea is to implement precisely that.
>
> example:
>
> COPY TO file_name USING some_select_statement;
I have run into plenty of cases where I wanted to dump part of a
structure and this could be used for that, but I've always found that
temporary tables were sufficient and equally SQL scriptable
CREATE TEMP TABLE tab AS SELECT ...; COPY tab TO file_name;
--
Rod Taylor wrote:
>>the problem is: COPY can write data returned by a SELECT statement to a
>>file. our idea is to implement precisely that.
>>
>>example:
>>
>>COPY TO file_name USING some_select_statement;
>
>
> I have run into plenty of cases where I wanted to dump part of a
> structure and this could be used for that, but I've always found that
> temporary tables were sufficient and equally SQL scriptable
>
> CREATE TEMP TABLE tab AS SELECT ...; COPY tab TO file_name;
Hi Rod,
TEMP TABLE are not suitable for my case. Using a temp table would
essentially mean that we had to store the data 3 times: Original data,
temp table + dump. Temp tables are only fine for small amounts of data
but we are talking about too much data here (my smallest export will
contain 15.000.000 records).
Best regards,
Hans
--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
Can't you just use a view?
-----Messaggio originale-----
Da: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]Per conto di Hans-Jürgen
Schönig
Inviato: mercoledì 21 settembre 2005 15.30
A: pgsql-hackers@postgresql.org; eg@cybertec.at
Oggetto: [HACKERS] feature proposal ...
hackers,
currently we have to hack tons of export scripts for various customers.
the problem is: if tables can be exported straight forward COPY will
give you all you need but when data has to be transformed while
exporting things start becoming a bit more complex. usually people want
to have CSV file (excel-ify data) which is supported by COPY.
the problem is: COPY can write data returned by a SELECT statement to a
file. our idea is to implement precisely that.
example:
COPY TO file_name USING some_select_statement;
the advantage would be that COPY would then be able to export data and
transform it on the fly. this would save many people a lot of work
because complex data extractors could in many cases be replaced by
simple SQL scripts.
how we plan to implement that:
currently copy simply opens a table and loops through the tuples (see
command/copy.c starting at line 1115).
to implement the desired feature we just had to add some SPI code to the
scenery (SPI will also return HeapTuples so it should fit in there).
Any comments?
Best regards,
Hans
--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
no because a new is not a heap ...
em=# create view x as select * from pg_class;
CREATE VIEW
em=# copy x to '/tmp/x';
ERROR: cannot copy from view "x"
best regards,
hans
Paolo Magnoli wrote:
> Can't you just use a view?
>
> -----Messaggio originale-----
> Da: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]Per conto di Hans-Jürgen
> Schönig
> Inviato: mercoledì 21 settembre 2005 15.30
> A: pgsql-hackers@postgresql.org; eg@cybertec.at
> Oggetto: [HACKERS] feature proposal ...
>
>
> hackers,
>
> currently we have to hack tons of export scripts for various customers.
> the problem is: if tables can be exported straight forward COPY will
> give you all you need but when data has to be transformed while
> exporting things start becoming a bit more complex. usually people want
> to have CSV file (excel-ify data) which is supported by COPY.
>
> the problem is: COPY can write data returned by a SELECT statement to a
> file. our idea is to implement precisely that.
>
> example:
>
> COPY TO file_name USING some_select_statement;
>
> the advantage would be that COPY would then be able to export data and
> transform it on the fly. this would save many people a lot of work
> because complex data extractors could in many cases be replaced by
> simple SQL scripts.
>
> how we plan to implement that:
> currently copy simply opens a table and loops through the tuples (see
> command/copy.c starting at line 1115).
> to implement the desired feature we just had to add some SPI code to the
> scenery (SPI will also return HeapTuples so it should fit in there).
>
> Any comments?
>
> Best regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde & Schönig GmbH
> Schöngrabern 134; A-2020 Hollabrunn
> Tel: +43/1/205 10 35 / 340
> www.postgresql.at, www.cybertec.at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> Paolo Magnoli wrote:
>> Can't you just use a view?
> no because a new is not a heap ...
I think Paolo's idea is much better than munging the syntax of COPY,
though. Fixing COPY so that you *could* copy from a view would provide
all the desired functionality without any syntactic warts.
regards, tom lane
Hans-Jürgen Schönig <postgres@cybertec.at> writes:
> COPY TO file_name USING some_select_statement;
I think this has been discussed before, check the archives.
> to implement the desired feature we just had to add some SPI code to the
> scenery (SPI will also return HeapTuples so it should fit in there).
Any implementation that depends on SPI here is wrong.
regards, tom lane
Joshua D. Drake wrote:
> Hans-Jürgen Schönig wrote:
>
>> no because a new is not a heap ...
>
>
> Why not use a function with a temporary table?
>
> That way you can pass a table parameter that
> is the temporary table with a select statement
> that you can populate the temp table with.
>
> Sincerely,
>
> Joshua D. Drake
>
hi joshua ...
temp tables are not an option - there is too much data around.
view are better here, i think ...
cheers,
hans
--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
Hans-Jürgen Schönig wrote: > no because a new is not a heap ... Why not use a function with a temporary table? That way you can pass a table parameter that is the temporary table with a select statement that you can populate the temp table with. Sincerely, Joshua D. Drake > > em=# create view x as select * from pg_class; > CREATE VIEW > > em=# copy x to '/tmp/x'; > ERROR: cannot copy from view "x" > > best regards, > > hans > > > > Paolo Magnoli wrote: > >> Can't you just use a view? >> >> -----Messaggio originale----- >> Da: pgsql-hackers-owner@postgresql.org >> [mailto:pgsql-hackers-owner@postgresql.org]Per conto di Hans-Jürgen >> Schönig >> Inviato: mercoledì 21 settembre 2005 15.30 >> A: pgsql-hackers@postgresql.org; eg@cybertec.at >> Oggetto: [HACKERS] feature proposal ... >> >> >> hackers, >> >> currently we have to hack tons of export scripts for various customers. >> the problem is: if tables can be exported straight forward COPY will >> give you all you need but when data has to be transformed while >> exporting things start becoming a bit more complex. usually people want >> to have CSV file (excel-ify data) which is supported by COPY. >> >> the problem is: COPY can write data returned by a SELECT statement to a >> file. our idea is to implement precisely that. >> >> example: >> >> COPY TO file_name USING some_select_statement; >> >> the advantage would be that COPY would then be able to export data and >> transform it on the fly. this would save many people a lot of work >> because complex data extractors could in many cases be replaced by >> simple SQL scripts. >> >> how we plan to implement that: >> currently copy simply opens a table and loops through the tuples (see >> command/copy.c starting at line 1115). >> to implement the desired feature we just had to add some SPI code to the >> scenery (SPI will also return HeapTuples so it should fit in there). >> >> Any comments? >> >> Best regards, >> >> Hans >> >> >> -- >> Cybertec Geschwinde & Schönig GmbH >> Schöngrabern 134; A-2020 Hollabrunn >> Tel: +43/1/205 10 35 / 340 >> www.postgresql.at, www.cybertec.at >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 6: explain analyze is your friend >> > > -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
So, that means copy doesn't support views? If it is like that, then why not
work in the View support for the Copy statement?
-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Hans-Jürgen Schönig
Sent: Miércoles, 21 de Septiembre de 2005 08:04 a.m.
To: Paolo Magnoli
Cc: pgsql-hackers@postgresql.org; eg@cybertec.at
Subject: Re: R: [HACKERS] feature proposal ...
no because a new is not a heap ...
em=# create view x as select * from pg_class;
CREATE VIEW
em=# copy x to '/tmp/x';
ERROR: cannot copy from view "x"
best regards,
hans
Paolo Magnoli wrote:
> Can't you just use a view?
>
> -----Messaggio originale-----
> Da: pgsql-hackers-owner@postgresql.org
> [mailto:pgsql-hackers-owner@postgresql.org]Per conto di Hans-Jürgen
> Schönig
> Inviato: mercoledì 21 settembre 2005 15.30
> A: pgsql-hackers@postgresql.org; eg@cybertec.at
> Oggetto: [HACKERS] feature proposal ...
>
>
> hackers,
>
> currently we have to hack tons of export scripts for various customers.
> the problem is: if tables can be exported straight forward COPY will
> give you all you need but when data has to be transformed while
> exporting things start becoming a bit more complex. usually people want
> to have CSV file (excel-ify data) which is supported by COPY.
>
> the problem is: COPY can write data returned by a SELECT statement to a
> file. our idea is to implement precisely that.
>
> example:
>
> COPY TO file_name USING some_select_statement;
>
> the advantage would be that COPY would then be able to export data and
> transform it on the fly. this would save many people a lot of work
> because complex data extractors could in many cases be replaced by
> simple SQL scripts.
>
> how we plan to implement that:
> currently copy simply opens a table and loops through the tuples (see
> command/copy.c starting at line 1115).
> to implement the desired feature we just had to add some SPI code to the
> scenery (SPI will also return HeapTuples so it should fit in there).
>
> Any comments?
>
> Best regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde & Schönig GmbH
> Schöngrabern 134; A-2020 Hollabrunn
> Tel: +43/1/205 10 35 / 340
> www.postgresql.at, www.cybertec.at
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--
Cybertec Geschwinde & Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's
datatypesdo not match
jd@commandprompt.com ("Joshua D. Drake") writes:
> Hans-Jürgen Schönig wrote:
>> no because a new is not a heap ...
>
> Why not use a function with a temporary table?
>
> That way you can pass a table parameter that
> is the temporary table with a select statement
> that you can populate the temp table with.
That means having to instantiate the temp table on disk "twice," once
as temp table, and once as the output file.
It would sure be nice to do it just once; that should lead to there
only being data written out once, which saves a lot on I/O.
--
(reverse (concatenate 'string "gro.mca" "@" "enworbbc"))
http://www.ntlug.org/~cbbrowne/spiritual.html
Rules of the Evil Overlord #59. "I will never build a sentient
computer smarter than I am." <http://www.eviloverlord.com/>
On Wednesday 2005-09-21 07:01, Hans-Jürgen Schönig wrote: > Rod Taylor wrote: > >>the problem is: COPY can write data returned by a SELECT statement to a > >>file. our idea is to implement precisely that. > >> > >>example: > >> > >>COPY TO file_name USING some_select_statement; > > > > I have run into plenty of cases where I wanted to dump part of a > > structure and this could be used for that, but I've always found that > > temporary tables were sufficient and equally SQL scriptable > > > > CREATE TEMP TABLE tab AS SELECT ...; COPY tab TO file_name; > > Hi Rod, > > TEMP TABLE are not suitable for my case. Using a temp table would > essentially mean that we had to store the data 3 times: Original data, > temp table + dump. Temp tables are only fine for small amounts of data > but we are talking about too much data here (my smallest export will > contain 15.000.000 records). Wouldn't you also need a CREATE TEMP TABLE privilege but the COPY TO file USING select_statement would only need select. (In other words using a temp table would not seem to be as secure nor as general as the requested feature.) Ideally COPYing from a view would be supported. As a user I like to treat a relation as a relation without having to worry about it's type. Nevertheless, there remains the issue of atomic permissions. One ought to be able to make selecting, copying, and creating views independent permissions for groups, roles, and users. A user should be able to copy and select without being able to create views. One can think of a table as a prototypical relation. Views are virtual tables. SELECT statements are ephemeral views. In a select statement you can use a proper table, a pre-defined view, or another select statement in the FROM or WHERE clause. Parallel behavior for COPY is reasonable.
On Wed, 2005-09-21 at 15:25 -0700, Trent Shipley wrote: > On Wednesday 2005-09-21 07:01, Hans-Jürgen Schönig wrote: > > Rod Taylor wrote: > > >>the problem is: COPY can write data returned by a SELECT statement to a > > >>file. our idea is to implement precisely that. > > >> > > >>example: > > >> > > >>COPY TO file_name USING some_select_statement; > > > > > > I have run into plenty of cases where I wanted to dump part of a > > > structure and this could be used for that, but I've always found that > > > temporary tables were sufficient and equally SQL scriptable > > > > > > CREATE TEMP TABLE tab AS SELECT ...; COPY tab TO file_name; > > > > Hi Rod, > > > > TEMP TABLE are not suitable for my case. Using a temp table would > > essentially mean that we had to store the data 3 times: Original data, > > temp table + dump. Temp tables are only fine for small amounts of data > > but we are talking about too much data here (my smallest export will > > contain 15.000.000 records). > > Wouldn't you also need a CREATE TEMP TABLE privilege but the > COPY TO file USING select_statement > would only need select. (In other words using a temp table would not seem to > be as secure nor as general as the requested feature.) Writing a file on the server requires significant privilege, including access to the server itself so you can retrieve the results. --
Rod Taylor wrote: >On Wed, 2005-09-21 at 15:25 -0700, Trent Shipley wrote: > > >> >>Wouldn't you also need a CREATE TEMP TABLE privilege but the >>COPY TO file USING select_statement >>would only need select. (In other words using a temp table would not seem to >>be as secure nor as general as the requested feature.) >> >> > >Writing a file on the server requires significant privilege, including >access to the server itself so you can retrieve the results. > > But we also do COPY to STDOUT which requires no special privileges on the server. Incidentally, if we are going to allow copy out from views, it would be nice and orthogonal to allow copy in too. Hasn't there been some talk about making automatically writeable views? cheers andrew
On Wed, 2005-09-21 at 19:55 -0400, Andrew Dunstan wrote: > > Rod Taylor wrote: > > >On Wed, 2005-09-21 at 15:25 -0700, Trent Shipley wrote: > > > > > >> > >>Wouldn't you also need a CREATE TEMP TABLE privilege but the > >>COPY TO file USING select_statement > >>would only need select. (In other words using a temp table would not seem to > >>be as secure nor as general as the requested feature.) > >> > >> > > > >Writing a file on the server requires significant privilege, including > >access to the server itself so you can retrieve the results. > > > > > > But we also do COPY to STDOUT which requires no special privileges on > the server. > > Incidentally, if we are going to allow copy out from views, it would be > nice and orthogonal to allow copy in too. Hasn't there been some talk > about making automatically writeable views? Sure. But if you are using STDOUT then why does this need to be a server side item at all? You either have code issuing the commands and collecting the results making a standard select just as fast or you are using psql which already has multiple display types for SELECT data, including XML output, but another could easily be added for CSV style output. --
Rod Taylor wrote: >You either have code issuing the commands and collecting the results >making a standard select just as fast or you are using psql which >already has multiple display types for SELECT data, including XML >output, but another could easily be added for CSV style output. > > > We have CSV output now and it's produced by the server. psql's \copy is in fact just a very thin veneer over the server-side COPY. Besides, we might well be using another client - your assertion that if COPY output is going to the client it must be psql is simply wrong. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Rod Taylor wrote:
>> Writing a file on the server requires significant privilege, including
>> access to the server itself so you can retrieve the results.
> But we also do COPY to STDOUT which requires no special privileges on
> the server.
Currently, we have a special privilege type about creating temporary
tables, which I think also restricts creating temporary views --- but
now that I think about it, it's not obvious why that should follow.
The only good argument I can see for restricting temp table creation
is that one might eat up large amounts of server disk space with a temp
table, and of course this argument doesn't apply to a temp view. So we
could refute this argument by just not making the permission check for
CREATE TEMP VIEW.
> Incidentally, if we are going to allow copy out from views, it would be
> nice and orthogonal to allow copy in too. Hasn't there been some talk
> about making automatically writeable views?
Sure, but until we actually have automatically writable views, it's a
bit premature to worry about that.
regards, tom lane
On K, 2005-09-21 at 20:34 -0400, Rod Taylor wrote: > Sure. But if you are using STDOUT then why does this need to be a server > side item at all? > > You either have code issuing the commands and collecting the results > making a standard select just as fast or you are using psql which > already has multiple display types for SELECT data, including XML > output, but another could easily be added for CSV style output. Another advantage of server-side COPY is that pgsql/libpq already does not try to collect the whole resultset in memory before starting the display/output process. -- Hannu Krosing <hannu@skype.net>
On Wed, 2005-09-21 at 11:31 -0400, Tom Lane wrote: > Hans-Jürgen Schönig <postgres@cybertec.at> writes: > > Paolo Magnoli wrote: > >> Can't you just use a view? > > > no because a new is not a heap ... > > I think Paolo's idea is much better than munging the syntax of COPY, > though. Fixing COPY so that you *could* copy from a view would provide > all the desired functionality without any syntactic warts. Well, I will probably help Juergen with the implementation. It seems that fetch data from VIEW is possible by portal stuff. Tom, do you think that there's any other (better) way how we can implement it? Karel -- Karel Zak <zakkr@zf.jcu.cz>
Tom Lane <tgl@sss.pgh.pa.us> writes: > So we could refute this argument by just not making the permission check for > CREATE TEMP VIEW. This is the first time I've ever heard of CREATE TEMP VIEW. What's the point of it since you can always directly do: SELECT * FROM (...) ? -- greg
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> So we could refute this argument by just not making the permission check for
>> CREATE TEMP VIEW.
> This is the first time I've ever heard of CREATE TEMP VIEW. What's the point
> of it since you can always directly do:
> SELECT * FROM (...)
> ?
Separation of concerns, for one thing: the code using the view need not
know the exact contents of the select, or perhaps not even have
permissions to access the underlying tables. (Think of a temp view
created by a SECURITY DEFINER function for instance.) In this
particular case it gives us separation of concerns in a different way,
ie, we don't have to figure out a way to force-fit the complete syntax
of SELECT inside a COPY command.
regards, tom lane
Added to TODO:
o Allow COPY to output from views
---------------------------------------------------------------------------
Andrew Dunstan wrote:
>
>
> Rod Taylor wrote:
>
> >On Wed, 2005-09-21 at 15:25 -0700, Trent Shipley wrote:
> >
> >
> >>
> >>Wouldn't you also need a CREATE TEMP TABLE privilege but the
> >>COPY TO file USING select_statement
> >>would only need select. (In other words using a temp table would not seem to
> >>be as secure nor as general as the requested feature.)
> >>
> >>
> >
> >Writing a file on the server requires significant privilege, including
> >access to the server itself so you can retrieve the results.
> >
> >
>
> But we also do COPY to STDOUT which requires no special privileges on
> the server.
>
> Incidentally, if we are going to allow copy out from views, it would be
> nice and orthogonal to allow copy in too. Hasn't there been some talk
> about making automatically writeable views?
>
> cheers
>
> andrew
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote: > Hans-Jürgen Schönig <postgres@cybertec.at> writes: > > Paolo Magnoli wrote: > >> Can't you just use a view? > > > no because a new is not a heap ... > > I think Paolo's idea is much better than munging the syntax of COPY, > though. Fixing COPY so that you *could* copy from a view would provide > all the desired functionality without any syntactic warts. While I'm all for COPY from views, I think I'd rather have the syntactic warts than code warts. ISTM that CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; COPY some_name TO stdout; is much uglier than COPY SELECT * FROM table WHERE ... TO stdout; -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
absolutely - the main advantage of the syntax tweak is that you can
add parameters more easily.
best regards,
hans
On 22 Sep 2005, at 21:25, Jim C. Nasby wrote:
> On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote:
>
>> Hans-Jürgen Schönig <postgres@cybertec.at>
>> writes:
>>
>>> Paolo Magnoli wrote:
>>>
>>>> Can't you just use a view?
>>>>
>>
>>
>>> no because a new is not a heap ...
>>>
>>
>> I think Paolo's idea is much better than munging the syntax of COPY,
>> though. Fixing COPY so that you *could* copy from a view would
>> provide
>> all the desired functionality without any syntactic warts.
>>
>
> While I'm all for COPY from views, I think I'd rather have the
> syntactic
> warts than code warts. ISTM that
>
> CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...;
> COPY some_name TO stdout;
>
> is much uglier than
>
> COPY SELECT * FROM table WHERE ... TO stdout;
> --
> Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
> Pervasive Software http://pervasive.com work: 512-231-6117
> vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
>
> > While I'm all for COPY from views, I think I'd rather have the > syntactic > warts than code warts. ISTM that > > CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; > COPY some_name TO stdout; > > is much uglier than > > COPY SELECT * FROM table WHERE ... TO stdout; Or, you could just allow subqueries in COPY to disambiguate the syntax: COPY (SELECT * FROM table WHERE i=1) TO stdout; |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|- AgentM agentm@themactionfaction.com |-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-
On N, 2005-09-22 at 21:34 +0200, Hans-Juergen Schoenig wrote: > absolutely - the main advantage of the syntax tweak is that you can > add parameters more easily. Perhaps "COPY from SQL FUNCTIONS" is what wou need ? Or should we piggypack on (future) work needed for hierarchical queries and have "COPY from WITH" like this. WITH copysource (f1,f2,f3) as (SELECT ... ) COPY copysource TO stdout; The full syntax (as a railroad diagram) of WITH for hierarchical queries is available at http://gppl.moonbone.ru/with_clause.gif . But with can be used also for non-hierarchical queries, as kind of inline temp view definition, and this copy syntax would be extension of this use. -- Hannu Krosing <hannu@skype.net>
Jim C. Nasby wrote: >While I'm all for COPY from views, I think I'd rather have the syntactic >warts than code warts. ISTM that > >CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; >COPY some_name TO stdout; > >is much uglier than > >COPY SELECT * FROM table WHERE ... TO stdout; > > They aren't mutually exclusive, though. And once you have code in place for the first part, turning the direct query case into a temp_view+copy is arguably just a case of syntactic sugar. I do think the direct query should at least be parenthesized, if we go that way. So why not do what everyone is agreed on now? Whatever happens the work won't be wasted. Also, as nifty as this might be, we should also be prepared for people to complain that it runs a lot slower than vanilla COPY, because it surely will. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> So why not do what everyone is agreed on now?
I wasn't agreed on it ;-)
The primary objection I've got is that I think this will be a very
considerable increment of work for exactly zero increment in
functionality, compared to being able to copy from a view. (If you're
not seeing why, consider that COPY is a utility statement not an
optimizable statement; you'd have to change that classification, with
resultant impacts all across the system.) There are other places
where the effort could be more usefully spent.
regards, tom lane
Tom Lane wrote: >Andrew Dunstan <andrew@dunslane.net> writes: > > >>So why not do what everyone is agreed on now? >> >> > >I wasn't agreed on it ;-) > >The primary objection I've got is that I think this will be a very >considerable increment of work for exactly zero increment in >functionality, compared to being able to copy from a view. (If you're >not seeing why, consider that COPY is a utility statement not an >optimizable statement; you'd have to change that classification, with >resultant impacts all across the system.) There are other places >where the effort could be more usefully spent. > > > > By "what everyone is agreed on" I meant "copy from a view". ;-) cheers andrew
AgentM wrote: > > > > While I'm all for COPY from views, I think I'd rather have the > > syntactic > > warts than code warts. ISTM that > > > > CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; > > COPY some_name TO stdout; > > > > is much uglier than > > > > COPY SELECT * FROM table WHERE ... TO stdout; > > Or, you could just allow subqueries in COPY to disambiguate the syntax: > > COPY (SELECT * FROM table WHERE i=1) TO stdout; This is one area where I think Informix did a better job than us, though we inherited COPY so I don't think we can fault the community. In Informix, LOAD is linked to INSERT, and UNLOAD to SELECT, so you do: LOAD FROM '/datafile' [optional flags]INSERT INTO tab [optional columns] and UNLOAD is: UNLOAD TO '/datafile' [optional flags]SELECT * FROM tab where the SELECT can use a column list, where clause, joins, etc. We could adopt something similar with COPY COPY FROM '/datafile' [optional flags]INSERT INTO tab [optional columns] COPY TO '/datafile' [optional flags]SELECT * FROM tab and internally use the non-executor COPY code for a simple INSERT/SELECT, and use the view/executor for more complex cases. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thursday 2005-09-22 13:16, Andrew Dunstan wrote: > Jim C. Nasby wrote: > >While I'm all for COPY from views, I think I'd rather have the syntactic > >warts than code warts. ISTM that > > > >CREATE TEMP VIEW some_name AS SELECT * FROM table WHERE ...; > >COPY some_name TO stdout; > > > >is much uglier than > > > >COPY SELECT * FROM table WHERE ... TO stdout; > > They aren't mutually exclusive, though. And once you have code in place > for the first part, turning the direct query case into a temp_view+copy > is arguably just a case of syntactic sugar. I do think the direct query > should at least be parenthesized, if we go that way. Definitely any SELECT that might occur in COPY should be a sub-select. It should meet any syntactic restrictions on a sub-select and it should be in parentheses (or for the liberal, implied parentheses). Proposed: o Allow COPY to output from views -- Pending "Allow COPY to output from views", Allow COPY to output from subqueries. The rationale being that all subqueries can be the create clause of a views. > So why not do what everyone is agreed on now? Whatever happens the work > won't be wasted. > > Also, as nifty as this might be, we should also be prepared for people > to complain that it runs a lot slower than vanilla COPY, because it > surely will. Why would there be a material difference in speed in the case of a simple projection? For example Given CREATE TABLE foo ( col_0 ,col_1 , . , . ,col_2N) Then COPY (SELECT col_0 ,col_2 , . , . ,col_2N) TO file-like-target