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

[Richard Broersma Jr]

--- Collin Peters <[EMAIL PROTECTED]> 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.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend