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

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

Post by jtocc » Wed, 09 Jul 2003 09:05:56


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?
 
 
 

1. w'S ***Hot stuff - check this out !!! w'S

2. Insert Into Select - I need each row Selected to be dependent on orecords previously inserted

I am building an effective dated file that shows results to date.

In part 1 of the select I
I process a larges set of input and genrate records for each date in the
processing period for which there has been activity

In part II I then want to select the most recent effective dated record (if
any and whether it existed before this run or was generated by an earlier
insert) and sum it with the record for this date

So my question is: when do the insertions happen and when does the selection
happen so that I can "see" the results of an earlier insertion?

I beleive that this is related to the problem I have seen dealt with before:

insert into tableA select * from tableB where not exists in tableA

I know this could be solved by doing a Select distinct on table B (see below
not that it matters) but I believe I saw a solution where there was a commit
immeadiate or some such thing that caused the exist clause to be evaluated
against tableA including records added earlier.









insert into tableA
select Distinct from tableB
where not exists in tableA

or

insert into tableA
select * from ( select Distinct from tableB ) as tableBB
where not exists in tableA

3. INSERT INTO (Multiple-record append query/Insert multiple records)

4. INSERT INTO (Multiple-record append query/Insert multiple records)

5. Insert Buttons on Toolbar for Insert Row and Insert Column grayed out

6. The select list for the INSERT statement contains fewer items than the insert list.

7. When I select to insert a page break, it is not inserting.

8. efficienci CURSOR+INSERT vs. INSERT INTO ... SELECT

9. How can i Insert multiple rows by using Insert statement in MS Acc

10. Use Insert Into to insert multiple records

11. Help ASP; get last inserted value from one table, insert multiple rows in another table.

12. Acces - insert multiple rows in a single sql-insert

13. Insert Multiple Records Using One Insert Statemen with MySQLdb module

14. insert multiple rows at once with a single insert command?

15. Multiple Insert or Looping Insert