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.