Thread: Inserting Data
Hi All
I have a basic problem that I hope can be addressed.
I need to insert data from one table into three other tables.
I attempted the following format.
CREATE OR REPLACE FUNCTION p_id_monitor()
RETURNS "trigger" AS
$$
Begin
insert into p_id.loops (monitor)
Select p_id.devices.devices_id
Where p_id.devices.device_number = library.devices.device_number
and library.devices.type_ = 'mon' ;
insert into p_id.settings (monitor)
Select p_id.devices.devices_id
Where p_id.devices.device_number = library.devices.device_number
and library.devices.type_ = 'mon' ;
insert into p_id.alarms (monitor)
Select p_id.devices.devices_id
Where p_id.devices.device_number = library.devices.device_number
and library.devices.type_ = 'mon' ;
Select p_id.devices.devices_id
Where p_id.devices.device_number = library.devices.device_number
and library.devices.type_ = 'mon' ;
Return Null ;
End;
$$
LANGUAGE 'plpgsql' ;
create trigger mon after insert on p_id.devices
for each row execute procedure p_id_monitor() ;
End;
$$
LANGUAGE 'plpgsql' ;
create trigger mon after insert on p_id.devices
for each row execute procedure p_id_monitor() ;
Unfortunately this gave multiple results on the target tables.
Is there a format that will give me a single insert for each original field without the need of creating three triggers???
Bob
On 8/18/06, Bob Pawley <rjpawley@shaw.ca> wrote: > Unfortunately this gave multiple results on the target tables. > > Is there a format that will give me a single insert for each original field > without the need of creating three triggers??? > > Bob try using old/new in your trigger functions. insert into table (targetfield) new.field; merlin
On Fri, Aug 18, 2006 at 09:27:19AM -0700, Bob Pawley wrote: > I need to insert data from one table into three other tables. > > I attempted the following format. [...] > insert into p_id.loops (monitor) > Select p_id.devices.devices_id > Where p_id.devices.device_number = library.devices.device_number > and library.devices.type_ = 'mon' ; Style recommendation: add a FROM clause to these queries. Missing FROM clauses are nonstandard and can cause unexpected results. PostgreSQL 8.0 and earlier allow such queries by default but in 8.1 they're disabled by default. See the add_missing_from configuration setting: http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#GUC-ADD-MISSING-FROM > Unfortunately this gave multiple results on the target tables. What do you mean by "multiple results"? Do you mean that each row inserted into p_id.devices causes multiple rows to be inserted into p_id.loops, p_id.settings, and p_id.alarms? The trigger function's query doesn't reference the new row that was inserted into p_id.devices; it joins the entire table against library.devices. Did you mean to do something like the following? INSERT INTO p_id.loops (monitor) SELECT NEW.devices_id FROM library.devices WHERE NEW.device_number = library.devices.device_number AND library.devices.type_ = 'mon'; -- Michael Fuhr
Hi Michael Yes - Multiple rows of the same data are created in each secondary table. I have two triggers that are identical in format although handling different tables. One is triggeres after insert and with this there is no multiplying factor. The other is triggered after an update. Both triggers use NEW.* in the same manner. However, the trigger after update gives multiple results of the same information. Is there any way around this problem? Is there perhaps a method restricting the trigger to an update to a particular column rather than the table as a whole? Bob ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Friday, August 18, 2006 7:27 PM Subject: Re: [GENERAL] Inserting Data > On Fri, Aug 18, 2006 at 09:27:19AM -0700, Bob Pawley wrote: >> I need to insert data from one table into three other tables. >> >> I attempted the following format. > [...] >> insert into p_id.loops (monitor) >> Select p_id.devices.devices_id >> Where p_id.devices.device_number = library.devices.device_number >> and library.devices.type_ = 'mon' ; > > Style recommendation: add a FROM clause to these queries. Missing > FROM clauses are nonstandard and can cause unexpected results. > PostgreSQL 8.0 and earlier allow such queries by default but in 8.1 > they're disabled by default. See the add_missing_from configuration > setting: > > http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#GUC-ADD-MISSING-FROM > >> Unfortunately this gave multiple results on the target tables. > > What do you mean by "multiple results"? Do you mean that each row > inserted into p_id.devices causes multiple rows to be inserted into > p_id.loops, p_id.settings, and p_id.alarms? The trigger function's > query doesn't reference the new row that was inserted into p_id.devices; > it joins the entire table against library.devices. Did you mean to > do something like the following? > > INSERT INTO p_id.loops (monitor) > SELECT NEW.devices_id > FROM library.devices > WHERE NEW.device_number = library.devices.device_number > AND library.devices.type_ = 'mon'; > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
On Mon, Aug 21, 2006 at 08:27:58AM -0700, Bob Pawley wrote: > Yes - Multiple rows of the same data are created in each secondary table. > > I have two triggers that are identical in format although handling > different tables. One is triggeres after insert and with this there is no > multiplying factor. > > The other is triggered after an update. The insert-vs-update distinction might be a red herring; the difference in behavior might be a result of the queries run inside the trigger functions. Or maybe the statements executed by the update trigger are firing additional triggers. Without more information we can only guess. > Both triggers use NEW.* in the same manner. However, the trigger after > update gives multiple results of the same information. How are the triggers using NEW? In your original message the function didn't use NEW at all. > Is there any way around this problem? Is there perhaps a method restricting > the trigger to an update to a particular column rather than the table as a > whole? Do you mean "particular row" instead of "particular column"? If you're executing INSERT ... SELECT statements from inside a trigger function as in your original message, then the restriction on the SELECT determines how many rows are inserted. It's possible that those inserts are causing additional triggers to fire. Have you added any RAISE statements to the trigger functions to see when they're being called? Could you post a simple, self-contained example that exhibits both the desired and undesired behavior? That is, all SQL statements that somebody could load into an empty database to create and populate the tables, create the triggers, and perform whatever actions are necessary to elicit both behaviors. -- Michael Fuhr
Hi Michael I set aside the procedure you sent to me as it resulted in multiple rows of the same information. (In fact one variation produced 100 rows for each of the 9 "new" fields creating a 900 row table. I went back to an earlier procedure which has been performing successfully. ------ create or replace function base() returns trigger as $$ begin insert into p_id.specifications (fluid_id) values (new.fluid_id); if new.ip_op_equipment = 'ip'or new.ip_op_equipment = 'op' then insert into p_id.pipes (fluid_id) values (new.fluid_id); elseif new.ip_op_equipment = 'eq' then insert into p_id.equipment (fluid_id) values (new.fluid_id); end if; return null; end; $$ language plpgsql ; create trigger fluid after insert on p_id.processes for each row execute procedure base(); ------------ In contrast here is the trigger for the tables with which I am now working. As best as I can determine the two triggers are the same format. Note the trigger is an 'after update' as opposed to 'after insert'. CREATE OR REPLACE FUNCTION p_id.valves_mon() RETURNS "trigger" AS $$ begin if new.type_ = 'end' then insert into p_id.association (valve) values (new.devices_id) ; elseif new.type_ = 'mon' then insert into p_id.loops (monitor) values (new.devices_id) ; end if ; return null; end ; $$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER loop AFTER UPDATE ON p_id.devices FOR EACH ROW EXECUTE PROCEDURE p_id.valves_mon(); This trigger results in three rows of each "new" field. I must admit I am having a little trouble fully understanding the basic PostgreSQL structure. What seems to me to be a logical procedure almost always has problems that I need to sort through. Help is greatly appreciated. Bob. ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Monday, August 21, 2006 4:47 PM Subject: Re: [GENERAL] Inserting Data > On Mon, Aug 21, 2006 at 08:27:58AM -0700, Bob Pawley wrote: >> Yes - Multiple rows of the same data are created in each secondary table. >> >> I have two triggers that are identical in format although handling >> different tables. One is triggeres after insert and with this there is no >> multiplying factor. >> >> The other is triggered after an update. > > The insert-vs-update distinction might be a red herring; the > difference in behavior might be a result of the queries run inside > the trigger functions. Or maybe the statements executed by the > update trigger are firing additional triggers. Without more > information we can only guess. > >> Both triggers use NEW.* in the same manner. However, the trigger after >> update gives multiple results of the same information. > > How are the triggers using NEW? In your original message the > function didn't use NEW at all. > >> Is there any way around this problem? Is there perhaps a method >> restricting >> the trigger to an update to a particular column rather than the table as >> a >> whole? > > Do you mean "particular row" instead of "particular column"? > > If you're executing INSERT ... SELECT statements from inside a > trigger function as in your original message, then the restriction > on the SELECT determines how many rows are inserted. It's possible > that those inserts are causing additional triggers to fire. Have > you added any RAISE statements to the trigger functions to see when > they're being called? > > Could you post a simple, self-contained example that exhibits both > the desired and undesired behavior? That is, all SQL statements > that somebody could load into an empty database to create and > populate the tables, create the triggers, and perform whatever > actions are necessary to elicit both behaviors. > > -- > Michael Fuhr
On Tue, Aug 22, 2006 at 09:02:52AM -0700, Bob Pawley wrote: > I set aside the procedure you sent to me as it resulted in multiple rows of > the same information. (In fact one variation produced 100 rows for each of > the 9 "new" fields creating a 900 row table. If it was doing that then it would be a good idea to understand why. If the INSERT ... SELECT matched several rows then several rows would be inserted, and if the trigger fired for several rows then several INSERTs would be run. > In contrast here is the trigger for the tables with which I am now working. > As best as I can determine the two triggers are the same format. > Note the trigger is an 'after update' as opposed to 'after insert'. [...] > This trigger results in three rows of each "new" field. What's the exact update command and how many rows in p_id.devices does it affect? If the update modifies three rows then the trigger will fire three times (because it's defined FOR EACH ROW), resulting in three inserts. That could explain the insert-vs-update difference because an ordinary insert affects only one row. If you add a RAISE statement to the trigger function then you'll see when and how many times it's being called. -- Michael Fuhr
Michael Perhaps we can look at the following as a simple example of what is happening- --------- create or replace function loop_association() returns trigger as $$ begin Insert Into p_id.loops (monitor) select new.devices_id from p_id.devices ; return null ; end ; $$ language plpgsql ; create trigger loop after insert on p_id.devices for each row execute procedure loop_association(); ------ This trigger and procedure gives a single row on the first insert on an otherwise blank table. However it produces two identical rows of the second device_id on the second insert and three identical rows of the third device_id on the third insert. (This is the only trigger on the table) If I read your message correctly the trigger is firing on each row of the originating table and each time it fires it produces a row on the secondary table for the current NEW.device_id. How can I correct this action? Bob ----- Original Message ----- From: "Michael Fuhr" <mike@fuhr.org> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Postgresql" <pgsql-general@postgresql.org> Sent: Tuesday, August 22, 2006 1:58 PM Subject: Re: [GENERAL] Inserting Data > On Tue, Aug 22, 2006 at 09:02:52AM -0700, Bob Pawley wrote: >> I set aside the procedure you sent to me as it resulted in multiple rows >> of >> the same information. (In fact one variation produced 100 rows for each >> of >> the 9 "new" fields creating a 900 row table. > > If it was doing that then it would be a good idea to understand > why. If the INSERT ... SELECT matched several rows then several > rows would be inserted, and if the trigger fired for several rows > then several INSERTs would be run. > >> In contrast here is the trigger for the tables with which I am now >> working. >> As best as I can determine the two triggers are the same format. >> Note the trigger is an 'after update' as opposed to 'after insert'. > [...] >> This trigger results in three rows of each "new" field. > > What's the exact update command and how many rows in p_id.devices > does it affect? If the update modifies three rows then the trigger > will fire three times (because it's defined FOR EACH ROW), resulting > in three inserts. That could explain the insert-vs-update difference > because an ordinary insert affects only one row. If you add a RAISE > statement to the trigger function then you'll see when and how many > times it's being called. > > -- > Michael Fuhr > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Bob Pawley <rjpawley@shaw.ca> writes: > Perhaps we can look at the following as a simple example of what is > happening- > --------- > create or replace function loop_association() returns trigger as $$ > begin > Insert Into p_id.loops (monitor) > select new.devices_id > from p_id.devices ; > return null ; > end ; > $$ language plpgsql ; > create trigger loop after insert on p_id.devices > for each row execute procedure loop_association(); > ------ > This trigger and procedure gives a single row on the first insert on an > otherwise blank table. However it produces two identical rows of the second > device_id on the second insert and three identical rows of the third > device_id on the third insert. (This is the only trigger on the table) Well, of course, because that's an unqualified "select", so each call will copy *all* of p_id.devices into p_id.loops. Methinks what you really want is to insert the NEW row, not the whole table. regards, tom lane
I thought the NEW qualified the select. If not, how is select qualified?? Thanks Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Michael Fuhr" <mike@fuhr.org>; "Postgresql" <pgsql-general@postgresql.org> Sent: Wednesday, August 23, 2006 3:18 PM Subject: Re: [GENERAL] Inserting Data > Bob Pawley <rjpawley@shaw.ca> writes: >> Perhaps we can look at the following as a simple example of what is >> happening- > >> --------- >> create or replace function loop_association() returns trigger as $$ >> begin > >> Insert Into p_id.loops (monitor) >> select new.devices_id >> from p_id.devices ; > >> return null ; >> end ; >> $$ language plpgsql ; > >> create trigger loop after insert on p_id.devices >> for each row execute procedure loop_association(); >> ------ > >> This trigger and procedure gives a single row on the first insert on an >> otherwise blank table. However it produces two identical rows of the >> second >> device_id on the second insert and three identical rows of the third >> device_id on the third insert. (This is the only trigger on the table) > > Well, of course, because that's an unqualified "select", so each call > will copy *all* of p_id.devices into p_id.loops. Methinks what you > really want is to insert the NEW row, not the whole table. > > regards, tom lane
Bob Pawley <rjpawley@shaw.ca> writes: > I thought the NEW qualified the select. Not at all; that would rather cripple the ability to write interesting triggers. I think what you are really wanting to do here is just insert into p_id.loops (monitor) values (new.devices_id); regards, tom lane
Thanks Tom But my problem with this solution comes whan I try to qualify with a 'where' clause. For instance - ---- create or replace function loop_association() returns trigger as $$ begin insert into p_id.loops (monitor) values (new.devices_id) where new.device_number = library.devices.device_number and library.devices.type_ = 'mon' ; return null ; end ; $$ language plpgsql ; create trigger loop after insert on p_id.devices for each row execute procedure loop_association(); ---- Gives me an error. What am I doing wrong? Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Michael Fuhr" <mike@fuhr.org>; "Postgresql" <pgsql-general@postgresql.org> Sent: Wednesday, August 23, 2006 3:31 PM Subject: Re: [GENERAL] Inserting Data > Bob Pawley <rjpawley@shaw.ca> writes: >> I thought the NEW qualified the select. > > Not at all; that would rather cripple the ability to write interesting > triggers. I think what you are really wanting to do here is just > > insert into p_id.loops (monitor) values (new.devices_id); > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq
Bob Pawley <rjpawley@shaw.ca> writes: > insert into p_id.loops (monitor) values (new.devices_id) > where new.device_number = library.devices.device_number > and library.devices.type_ = 'mon' ; Huh? How did library.devices get into this? Are you trying to join to it, and if so why, seeing that the value you want to insert into p_id.loops is independent of that table? regards, tom lane
What I have is one table which stores device_id numbers that are referenced on the second table "library.devices". I need to insert device_ids from the first table that satisfy the conditions of the argument found on the library table. Hence the 'where' clause. So far all I can get are errors when I attempt this procedure. Hence - my problem. Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Michael Fuhr" <mike@fuhr.org>; "Postgresql" <pgsql-general@postgresql.org> Sent: Wednesday, August 23, 2006 4:05 PM Subject: Re: [GENERAL] Inserting Data > Bob Pawley <rjpawley@shaw.ca> writes: >> insert into p_id.loops (monitor) values (new.devices_id) >> where new.device_number = library.devices.device_number >> and library.devices.type_ = 'mon' ; > > Huh? How did library.devices get into this? Are you trying to join to > it, and if so why, seeing that the value you want to insert into > p_id.loops is independent of that table? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
Bob Pawley <rjpawley@shaw.ca> writes: > What I have is one table which stores device_id numbers that are referenced > on the second table "library.devices". > I need to insert device_ids from the first table that satisfy the conditions > of the argument found on the library table. Hence the 'where' clause. This isn't real clear to me, but perhaps you are looking for something like IF EXISTS(select 1 from library.devices where ...) THEN INSERT INTO ... values(new.device_id); END IF; regards, tom lane
I'm not arguing (I'm attempting to learn) - but this seems to be counter intuitive when writing a procedure. I know that it exists because, through the interface, I have selected it from the same library table. Could you explain why Postgresql simply doesn't accept the simple 'where' statement that was in my earlier e-mail. Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Michael Fuhr" <mike@fuhr.org>; "Postgresql" <pgsql-general@postgresql.org> Sent: Wednesday, August 23, 2006 4:44 PM Subject: Re: [GENERAL] Inserting Data > Bob Pawley <rjpawley@shaw.ca> writes: >> What I have is one table which stores device_id numbers that are >> referenced >> on the second table "library.devices". >> I need to insert device_ids from the first table that satisfy the >> conditions >> of the argument found on the library table. Hence the 'where' clause. > > This isn't real clear to me, but perhaps you are looking for something > like > > IF EXISTS(select 1 from library.devices where ...) THEN > INSERT INTO ... values(new.device_id); > END IF; > > regards, tom lane
Let me explain. I have a table called p_id.devices which accumulates the devices_id for a multitude of differing devices used in P&ID development.(Process Engineering) I also have a table called library.devices which is ( or soon will be ) a detailed explanation of all of the particular devices available. I accumulate the device_ids of the devices used during the P&ID development. What I need to do now is distribute the various devices to their own tables (Loops as well as others) based on the information found in the library.devices table. I'm trying to make best use of the relationship features of a relational data base. However, I am frustrated by what appears to be a restrictive use of simple logic. I am sure there is a reason for developing general SQL and PostgreSQL in the manner in which it has developed. I am just trying to parse the details behind the structure as best I can. Bob ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Bob Pawley" <rjpawley@shaw.ca> Cc: "Michael Fuhr" <mike@fuhr.org>; "Postgresql" <pgsql-general@postgresql.org> Sent: Wednesday, August 23, 2006 4:44 PM Subject: Re: [GENERAL] Inserting Data > Bob Pawley <rjpawley@shaw.ca> writes: >> What I have is one table which stores device_id numbers that are >> referenced >> on the second table "library.devices". >> I need to insert device_ids from the first table that satisfy the >> conditions >> of the argument found on the library table. Hence the 'where' clause. > > This isn't real clear to me, but perhaps you are looking for something > like > > IF EXISTS(select 1 from library.devices where ...) THEN > INSERT INTO ... values(new.device_id); > END IF; > > regards, tom lane
On Wed, Aug 23, 2006 at 05:34:27PM -0700, Bob Pawley wrote: > Could you explain why Postgresql simply doesn't accept the simple 'where' > statement that was in my earlier e-mail. Because INSERT doesn't take a WHERE clause. If you want to do the insert conditionally then use an IF statement as Tom suggested or use INSERT ... SELECT with a WHERE clause that would restrict the SELECT result to an empty set if the insert shouldn't happen. -- Michael Fuhr
On Wed, Aug 23, 2006 at 07:34:43PM -0700, Bob Pawley wrote: > Let me explain. I'll build a simple example based on what you describe. Please make corrections as necessary. > I have a table called p_id.devices which accumulates the devices_id for a > multitude of differing devices used in P&ID development.(Process > Engineering) CREATE TABLE p_id.devices ( devices_id integer ); > I also have a table called library.devices which is ( or soon will be ) a > detailed explanation of all of the particular devices available. CREATE TABLE library.devices ( device_number integer, type_ text ); > I accumulate the device_ids of the devices used during the P&ID > development. What I need to do now is distribute the various devices to > their own tables (Loops as well as others) based on the information found > in the library.devices table. I'm trying to make best use of the > relationship features of a relational data base. CREATE TABLE p_id.loops ( monitor integer ); CREATE TABLE p_id.settings ( monitor integer ); CREATE TABLE p_id.alarms ( monitor integer ); > However, I am frustrated by what appears to be a restrictive use of simple > logic. I am sure there is a reason for developing general SQL and > PostgreSQL in the manner in which it has developed. I am just trying to > parse the details behind the structure as best I can. If the above CREATE TABLE statements are correct as far as the relevant columns are concerned then please post some INSERT statements that will set up an initial state. If I understand correctly then that would be some records inserted into library.devices and nothing (yet) in the other tables. Once we've established the initial state then we'll consider what happens next. If I understand then that would be inserts and updates into p_id.devices. Please show some INSERT and UPDATE statements and describe what effect those statements should have on p_id.loops, p_id.settings, and/or p_id.alarms. For example: INSERT INTO p_id.devices (devices_id) VALUES (1); -- such-and-such should happen in p_id.loops -- such-and-such should happen in p_id.settings -- such-and-such should happen in p_id.alarms INSERT INTO p_id.devices (devices_id) VALUES (2); -- such-and-such should happen in p_id.loops -- such-and-such should happen in p_id.settings -- such-and-such should happen in p_id.alarms UPDATE p_id.devices SET column_name = new_value WHERE some_condition; -- such-and-such should happen in p_id.loops -- such-and-such should happen in p_id.settings -- such-and-such should happen in p_id.alarms Once we have a clear picture of what should happen in response to what actions then it'll be easier to figure out how to make that happen. -- Michael Fuhr