From: Richard Broersma Jr <rabroersma@yahoo.com>
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Insert into VIEW using RULE. Not possible to use nextval()?
Date: Thu, 3 May 2007 12:26:42 -0700 (PDT)
Message-ID: <13300.9416.qm@web31809.mail.mud.yahoo.com> (raw)
In-Reply-To: <df01c91b0705031130qc75dfd5qc58e5c9d6cba33bf@mail.gmail.com>
--- 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,
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, cadiolis@gmail.com
Subject: Re: Insert into VIEW using RULE. Not possible to use nextval()?
In-Reply-To: <13300.9416.qm@web31809.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox