469,647 Members | 1,784 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,647 developers. It's quick & easy.

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

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
0 2944

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Juho Saarikko | last post: by
3 posts views Thread by Jason Callas | last post: by
1 post views Thread by Jim | last post: by
3 posts views Thread by DarthMacgyver | last post: by
1 post views Thread by dkode8 | last post: by
2 posts views Thread by Scott Goodwin | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.