Обсуждение: Inserting Data

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