CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
----- Original Message -----
From: "Adrian Klaver" <aklaver@xxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: Insert
On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
I have two tables - p_id.association and p_id.devices
If a new row in p_id.association has a value - say 2 in column
mon_function
and a value 5 in column monitoring_fluid I want the new value for
mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not
appear in the same row in p_id.devices.
The following gives me a return of "more than one row" and I can't figure
out what's wrong.
First could you send the actual CREATE FUNCTION statement. I will assume
you
are using pl/pgsql.
Second I am assuming this is a trigger function, so the CREATE TRIGGER
statement would be useful.
Any thoughts would be appreciated.
Bob
Declare
xmon_function varchar;
Begin
Select mon_function into xmon_function
From p_id.association
Where mon_function = new.mon_function;
If this is a trigger function, the above is redundant. Just use
new.mon_function.
If xmon_function = p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
Then
You can't do this. You would need to do something along lines of SELECT
p_id.devices.device_number INTO dev_no FROM ... and
then IF new.mon_function = dev_no THEN
Return Null;
Elseif xmon_function = p_id.devices.device_number
or xmon_function != p_id.devices.device_number
From p_id.association, p_id.devices
Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
or p_id.association.monitoring_fluid != p_id.devices.pipe_id
See above.
Then
Insert into p_id.devices (device_number) Values (New.mon_function) ;
Return Null;
END if;
End;
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
aklaver@xxxxxxxxxxx
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
