473,386 Members | 1,715 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 software developers and data experts.

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 3110

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Juho Saarikko | last post by:
I made a Python script which takes Usenet message bodies from a database, decodes uuencoded contents and inserts them as Large Object into a PostGreSQL database. However, it appears that the to...
3
by: Jason Callas | last post by:
I have a stored procedure that runs as a step in a scheduled job. For some reason the job does not seem to finish when ran from the job but does fine when run from a window in SQL Query. I know...
1
by: Jim | last post by:
For some reason the compiler is telling me that I must declarethe variable @costcenter_tmp on lines 74 and 98...but if i put a select statement in ther (for testing) before the loop I get data back...
3
by: DarthMacgyver | last post by:
Hello, I recently wrote a survey application. Each question is very similar. The first questions gives me a problem when there are multiple people taking the survey (The Database connection...
1
by: dkode8 | last post by:
Heres my problem, the first part selects a row from the database, if there is no row with the criteria it inserts a row and then returns it, the problem is the IF statement that inserts the row,...
10
by: Florian G. Pflug | last post by:
Hi I installed a postgres-application (which was developed on debian woody) on red hat 9 today, using the postgres 7.3 rpms from redhad. One of my the triggers uses the pg_settings table (more...
2
by: Scott Goodwin | last post by:
The following SQL: create table toinherit ( id integer primary key ); create table leftside ( leftname varchar(64) not null unique ) inherits (toinherit);
0
by: ramuygl | last post by:
want to create store procedure that. want to send the table name as argument and retrive the data of that argument. and want to store data in temperary table using the insert query. HERE I AM...
58
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.