Re: Insert into VIEW using RULE. Not possible to use nextval()?

--- Collin Peters <cadiolis(at)gmail(dot)com> wrote:

> The exact problem is that you can't use nextval() with an INSERTable VIEW

I apoligize if I am not understanding your problem correctly. I am unsure as to intent behind
using nextval() as opposed to currval().

I do not know if the following DDL will help but I can show you how I preform inserts across
multiple tables using the rule system:

CREATE RULE Vschematic_insert AS ON INSERT TO Docs.Vschematic
DO INSTEAD (
INSERT INTO Docs.Document ( did, doccode, docnum, docdisc)
VALUES ( DEFAULT, 'schematic', New.docnum, New.docdisc);

INSERT INTO Docs.Drawing ( did, doccode, title1, title2, title3)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic', New.title1, New.title2, New.title3);

INSERT INTO Docs.Schematic ( did, doccode)
VALUES ( Currval('Docs.Document_did_seq'), 'schematic')
);

For reference the table DDL follows:

CREATE TABLE docs.document (
did serial PRIMARY KEY,
doccode varchar(30) not null,
docnum varchar(30) unique not null,
docdisc text not null default '',

constraint
document_doccode_chk
check ( doccode in ( 'cpf', 'logicsystem', 'processdetail',
'electricaldetail', 'locationplan', 'logicdiagram',
'loopdiagram', 'schematic', 'wiringdiagram', 'pid',
'isometric', 'airsupplydetail', 'mountingdetail',
'pnuematicdetail', 'functionaldiscription',
'datasheet', 'processmaterialspec',
'loopfoldermiscellaneous', 'loopfolderorficeplate',
'loopfolderinstallation', 'loopfolderswitch',
'loopfolderxmtrctrlind', 'loopfoldercontrolvalve',
'loopfolderanalyzer', 'loopfolderworkscope',
'loopfolderdocumentation')));

CREATE TABLE docs.drawing
(
did integer primary key references
docs.document(did) on delete cascade,
doccode varchar(30) not null,
title1 varchar(50) not null,
title2 varchar(50) not null,
title3 varchar(50) not null,

constraint
drawing_doccode_chk
check ( doccode in ( 'processdetail', 'electricaldetail', 'locationplan',
'logicdiagram', 'loopdiagram', 'schematic', 'pid',
'isometric', 'airsupplydetail', 'mountingdetail',
'pnuematicdetail'))) ;

CREATE TABLE docs.schematic
(
did integer primary key references
docs.drawing(did) on delete cascade,
doccode varchar(30) not null,
cid integer references equ.lcp(cid),

constraint
schematic_doccode_chk
check ( doccode = 'schematic')) ;

Regards,
Richard Broersma Jr.