feature proposal ...

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

From thhal at mailblocks.com Wed Sep 21 14:54:13 2005
From: thhal at mailblocks.com (Thomas Hallgren)
Date: Wed, 21 Sep 2005 16:54:13 +0200
Subject: [Pljava-dev] Where is pgxs?
References: <43317415.7090503@mailblocks.com>
<thhal-0mAwNBEFQ8bQWDbfABskJWLlSNUKoz+@mailblocks.com>
Message-ID: <thhal-0mQwNBENQ8bQ5KN/zhiJaVVpTCADzIC@mailblocks.com>

Hi,
I just installed the PostgreSQL 8.1-beta2 on my Windows box using the
windows installer. I then try to compile PL/Java. It fails to find pgxs.
Is it moved to somewhere else? Or is it not included?

Regards,
Thomas Hallgren

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

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

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/

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

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

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/&gt;

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:

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

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

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

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:

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

From: Andrew Dunstan [mailto:andrew@dunslane.net]
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.

At which point we point out to them that it's also much faster than any of the other alternatives. :)

But yes, we should mention it in the docs, if for no other reason than to help prevent people from doing COPY (SELECT * FROM table) TO ...

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

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