From: Collin Peters <cadiolis@gmail.com>
To: Richard Broersma Jr <rabroersma@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Insert into VIEW using RULE. Not possible to use nextval()?
Date: Fri, 4 May 2007 14:51:00 -0700
Message-ID: <df01c91b0705041451j1514eaa4i2b68a4f4b6331384@mail.gmail.com> (raw)
In-Reply-To: <13300.9416.qm@web31809.mail.mud.yahoo.com>
References: <df01c91b0705031130qc75dfd5qc58e5c9d6cba33bf@mail.gmail.com>
<13300.9416.qm@web31809.mail.mud.yahoo.com>
Hi Richard,
Your situation is the same as mine. The only difference is that I
actually had a call to nextval() in the actual call to the VIEW. In
your case this would be something like "INSERT INTO
Vschematic_insert(...". So if you were to try "INSERT INTO
Vschematic_insert(nextval('foo_sql'), ..." and then try to use that
value inside the RULE it will fail.
Your solution works great assuming two things:
1. You don't need to use the ID for anything else (which is true in
your case and actually in mine as well)
2. currval() is guaranteed to be correct within the RULE. This was my
second question. If I call "INSERT INTO Vschematic_insert(..."
simultaneously 1000 times is it guaranteed to be correct?
On 5/3/07, Richard Broersma Jr <rabroersma@yahoo.com> wrote:
>
> --- 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: cadiolis@gmail.com, rabroersma@yahoo.com
Subject: Re: Insert into VIEW using RULE. Not possible to use nextval()?
In-Reply-To: <df01c91b0705041451j1514eaa4i2b68a4f4b6331384@mail.gmail.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