DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=Q62Qm4jfX2wTt7JoYxNnl8om4V629QYWzb+l6w+nl1Y0YvfYNpqr9dMuGw+CwmW6Cs1bZySGCMOYxzQEoX/tL44mFuCF6Vhfoz2GrmBIVkSfO/pPkM1Lm8q791TeKp1F4DwSOfVg6iwf9+4nbyeWSOq7tAYgi5WK37i+eMYL8/A=;
Date: Thu, 3 May 2007 12:26:42 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Insert into VIEW using RULE. Not possible to use nextval()?
To: Collin Peters <cadiolis@gmail.com>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <df01c91b0705031130qc75dfd5qc58e5c9d6cba33bf@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <13300.9416.qm@web31809.mail.mud.yahoo.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.