Re: COPY into a view; help w. design & patch

On 05/18/2007 07:05:50 PM, Robert Haas wrote:
> I'm not sure exactly why you want to do with this, but it seems very
> similar to what you can already do using prepared statements. Instead
> of saying
>
> COPY INTO (statement) (column [, ...])
> a1,a2,a3,...,an
> b1,b2,b3,...,bn
> c1,c2,c3,...,cn
> \.

I don't really want to do this. I really want my users
to be able to use the COPY statement without worrying
about whether they are copying into a table or a view.

COPY tableorview (column [, ...]) FROM stdin;
a1,a2,a3,...,an
b1,b2,b3,...,bn
c1,c2,c3,...,cn
\.

I just wanted to cover all the options when going over
the design choices, so came up with the COPY INTO
syntax.

> You could instead say:
>
> BEGIN WORK
> PREPARE somestatementhandle (column[, ...]) AS statement
> EXECUTE somestatementhandle ('a1','a2','a3','...','an');
> EXECUTE somestatementhandle ('b1','b2','b3','...','bn');
> EXECUTE somestatementhandle ('c1','c2','c3','...','cn');
> DEALLOCATE somestatementhandle
> COMMIT WORK

The trouble is that my users have data, in excel spreadsheets
and the like, and it needs to get into PostgreSQL.
The data they have corresponds to views made in the db, but
not to tables. I _could_ make tables that "correspond"
to the views and put BEFORE INSERT triggers on them and
have the triggers insert into the views (or the equalivent),
but then the users would have to use the views for most
things and the "corresponding tables" when doing a COPY
or using the application's data import function. That's
not so good for lots of reasons. Of course I could always
write a special application for each view to import into
each view, but why not have the COPY command there to do
it for me?

Karl <kop(at)meme(dot)com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein