Skip site navigation (1) Skip section navigation (2)

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

From: "Karl O(dot) Pinc" <kop(at)meme(dot)com>
To: Robert Haas <Robert(dot)Haas(at)dyntek(dot)com>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: COPY into a view; help w. design & patch
Date: 2007-05-19 02:48:20
Message-ID: 1179542900l.12174l.1l@mofo (view raw or flat)
Thread:
Lists: pgsql-hackers
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


In response to

Responses

pgsql-hackers by date

Next:From: Karl O. PincDate: 2007-05-19 03:22:41
Subject: Re: Signing off of patches (was Re: Not ready for 8.3)
Previous:From: Tom LaneDate: 2007-05-19 01:59:11
Subject: Re: Signing off of patches (was Re: Not ready for 8.3)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group