Thread

Re: [SQL] insertable views - not copy-able ?

Herouth Maoz <herouth@oumail.openu.ac.il> — 1999-10-19T13:25:19Z

At 22:56 +0200 on 17/10/1999, =?iso-8859-2?Q?Daniel_P=E9der?= wrote:


> what about this:
> ( it would be nice to have it working, specially for copying values from
>files into table with default fields, having the default fields doing
>their job or initialising tables using reduced set of columns )
>
> mydb=> create sequence MYSEQ;
> CREATE
> mydb=> create table MYTAB ( ID int4 default nextval('MYSEQ'), NAME text );
> CREATE
> mydb=> create view MYVIEW as select name from MYTAB;
> CREATE
> mydb=> copy MYVIEW from stdin;

Seems this view is neither insertable nor copyable. To make it insertable,
you have to define a rule, you know.

In any case, I don't think it would work for copy - the rule I mean.

IMO, if you want to copy data and have defaults work, you copy the data
into a temporary table with only the necessary fields, and then issue an
insert:

CREATE TEMP TABLE tmp_tab ( name text );
COPY tmp_tab FROM stdin;
jim
john
jack
\.
INSERT INTO mytab (name) SELECT name FROM tmp_tab;
DROP TABLE tmp_tab;

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma