Re: Insert into VIEW using RULE. Not possible to use nextval()? - Mailing list pgsql-sql

--- Collin Peters <cadiolis@gmail.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,
 
constraintdrawing_doccode_chkcheck     ( 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),
 
constraintschematic_doccode_chkcheck ( doccode = 'schematic')) ;

Regards,
Richard Broersma Jr.