Horton

Thread

  1. feature proposal ...

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2005-09-21T13:29:45Z

    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

  2. Re: feature proposal ...

    Rod Taylor <pg@rbt.ca> — 2005-09-21T13:46:00Z

    > 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;
--

  3. Re: feature proposal ...

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2005-09-21T14:01:30Z

    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

  4. R: feature proposal ...

    pmagnoli@systemevolution.it — 2005-09-21T14:03:17Z

    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

  5. Re: R: feature proposal ...

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2005-09-21T14:04:01Z

    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

  6. Re: feature proposal ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-21T15:28:31Z

    =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <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

  7. Re: R: feature proposal ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-21T15:31:42Z

    =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <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

  8. Re: R: feature proposal ...

    Joshua D. Drake <jd@commandprompt.com> — 2005-09-21T15:47:42Z

    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/

  9. Re: R: feature proposal ...

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2005-09-21T15:55:29Z

    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

  10. Re: R: feature proposal ...

    Cristian Prieto <cristian@clickdiario.com> — 2005-09-21T16:52:54Z

    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 datatypes do not
       match

  11. Re: R: feature proposal ...

    Chris Browne <cbbrowne@acm.org> — 2005-09-21T17:10:52Z

    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/>

  12. Re: feature proposal ...

    Trent Shipley <tshipley@deru.com> — 2005-09-21T22:25:47Z

    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.

  13. Re: feature proposal ...

    Rod Taylor <pg@rbt.ca> — 2005-09-21T22:32:23Z

    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.
--

  14. Re: feature proposal ...

    Andrew Dunstan <andrew@dunslane.net> — 2005-09-21T23:55:38Z

    
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

  15. Re: feature proposal ...

    Rod Taylor <pg@rbt.ca> — 2005-09-22T00:34:16Z

    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.

--

  16. Re: feature proposal ...

    Andrew Dunstan <andrew@dunslane.net> — 2005-09-22T01:02:38Z

    
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

  17. Re: feature proposal ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-22T03:09:38Z

    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

  18. Re: feature proposal ...

    Hannu Krosing <hannu@skype.net> — 2005-09-22T05:41:19Z

    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>

  19. Re: R: feature proposal ...

    Karel Zak <zakkr@zf.jcu.cz> — 2005-09-22T10:36:21Z

    On Wed, 2005-09-21 at 11:31 -0400, Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <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>

  20. Re: feature proposal ...

    Greg Stark <gsstark@mit.edu> — 2005-09-22T13:15:23Z

    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

  21. Re: feature proposal ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-22T14:40:20Z

    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

  22. Re: feature proposal ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-09-22T16:01:36Z

    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, Pennsylvania 19073

  23. Re: R: feature proposal ...

    Jim C. Nasby <jnasby@pervasive.com> — 2005-09-22T19:25:53Z

    On Wed, Sep 21, 2005 at 11:31:42AM -0400, Tom Lane wrote:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <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

  24. Re: R: feature proposal ...

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2005-09-22T19:34:30Z

    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:
>
>> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <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
>

  25. Re: R: feature proposal ...

    A.M. <agentm@themactionfaction.com> — 2005-09-22T19:47:00Z

    >
> 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
|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-

  26. Re: R: feature proposal ...

    Hannu Krosing <hannu@skype.net> — 2005-09-22T20:08:59Z

    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>

  27. Re: R: feature proposal ...

    Andrew Dunstan <andrew@dunslane.net> — 2005-09-22T20:16:57Z

    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

  28. Re: R: feature proposal ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2005-09-22T20:25:53Z

    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

  29. Re: R: feature proposal ...

    Andrew Dunstan <andrew@dunslane.net> — 2005-09-22T20:39:10Z

    
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

  30. Re: R: feature proposal ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2005-09-22T21:53:44Z

    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, Pennsylvania 19073

  31. Re: R: feature proposal ...

    Trent Shipley <tshipley@deru.com> — 2005-09-22T23:34:16Z

    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