Обсуждение: insertable views - not copy-able ?
what about this: ( it would be nice to have it working, specially for copying values from files into table with default fields, having thedefault 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; Enter info followed by a newline End with a backslash and a period on a line by itself. >> jim >> john >> jack >> \. mydb=> select MYVIEW.*; name ---- (0 rows) -- dan peder dpeder@infoset.cz
> > what about this: > ( it would be nice to have it working, specially for copying values from files into table with default fields, having thedefault 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; First this setup wouldn't work with INSERT too. The INSTEAD rule for INSERT is missing. Second COPY isn't a rewritable statement, and it will not become such since only commands that have a rangetable and a targetlist can be handled by the rewriter at all. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #========================================= wieck@debis.com (Jan Wieck) #
At 22:56 +0200 on 17/10/1999, Daniel Péder 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