By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,302 Members | 1,185 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,302 IT Pros & Developers. It's quick & easy.

problem w/CREATE RULE...ON INSERT...INSERT INTO...SELECT...FROM...WHERE w/multiple inserts

P: n/a
I'm having a big problem with CREATE RULE...ON INSERT...INSERT
INTO...SELECT...FROM...WHERE when I want to INSERT several (20~50)
records based on a single INSERT to a view. Either I get a 'too much
data for field' or the query just runs on and on til I have to restart
the postmaster.

I have found rules to compare mine to but people limit the resulting
insert to one record (the WHERE generally limits the result of the
SELECT to one record by matching an ID or a UNIQUE INDEX). But I
believe this should work because I've done ON INSERT...UPDATE...SELECT
and _updated_ many records. Go figure.

If anyone could post a similar query that they got to work I would be
eternally grateful.

If not, I'm hoping someone will sling some advice my way...
---------------------------------------------------
CREATE TABLE public.ttransaction (
id int8 DEFAULT nextval('"ttransaction_id_key"'::text) NOT NULL,
ttype varchar(1) NOT NULL,
item varchar(50) NOT NULL,
quantity int4 NOT NULL,
disposition varchar(30) NOT NULL,
cost numeric(15, 2) DEFAULT 0 NOT NULL,
vendor varchar(50),
tstamp date DEFAULT ('now'::text)::timestamp(0) with time zone NOT
NULL,
CONSTRAINT ttransaction_pk PRIMARY KEY (id)
) WITH OIDS;
------------------------------------------------------
CREATE VIEW public.rule_ttransaction AS SELECT id, ttype, item,
quantity, disposition, cost, vendor, tstamp FROM ttransaction;
------------------------------------------------------
CREATE RULE on_order AS ON INSERT TO rule_ttransaction
WHERE (new.ttype = 'O'::character varying) DO

INSERT INTO ttransaction
(ttype, item, quantity, disposition, cost, vendor)

SELECT new.ttype, tbl."Item", (tbl."ItemQty" * new.quantity),
new.disposition, tbl."AveCost", new.vendor

FROM (SELECT tnewbom."KitPartNumber", tnewbom."Item",
tnewbom."ItemQty", titemmaster."AveCost" FROM (tnewbom INNER JOIN
titemmaster ON (t tnewbom."Item"= titemmaster."Item"))) tbl

WHERE (tbl."KitPartNumber" = new.item);
------------------------------------------------------
I also have a rule ON UPDATE to view rule_transaction set to DO
NOTHING.
------------------------------------------------------

I am trying to insert multiple rows into ttransaction--one for every
result of the SELECT FROM tnewbom INNER JOIN titemmaster AND WHERE
tbl."KitPartNumber" = new.item.
I'm on 7.3.3. Here's a simpler version that still didn't work:

---------------------------------------------------
CREATE RULE on_order AS ON INSERT TO rule_ttransaction
WHERE (new.ttype = 'O'::character varying) DO

INSERT INTO ttransaction
(ttype, item, quantity, disposition, cost, vendor)

SELECT new.ttype, tbl."Item", (tbl."ItemQty" * new.quantity),
new.disposition, tbl."AveCost", new.vendor

FROM (SELECT tnewbom."KitPartNumber", tnewbom."Item",
tnewbom."ItemQty" FROM tnewbom) tbl

WHERE (tbl."KitPartNumber" = new.item);

-----------------------------
jtocci, fort wayne, in
I was born in the same town as Johnny Appleseed and now I live in the
city where he's buried. Should I worry?
Nov 11 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.