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?
