Inserting Data

Michael

You did well interpreting my scribblings.

In attempting to use the IF expression (below) I receive an error message stating the return includes two or more rows. This seems to make sense since I am asking if one condition exists (p_id device_number = library device_number then the return should be all the rows that equal 'mon' (or 'end').

Is there a method of modifying the IF expression to look only at the row in which device_number equals device_number. (I've tried changing AND to WHERE with no affect.

Thanks for your help.

Bob

CREATE TABLE p_id.devices (
devices_id integer
);

CREATE TABLE library.devices (
device_number integer,
type_ varchar
);
Insert Into library.devices (device_number, type_)
Values ('1', 'mon' ) ;
Insert Into library.devices (device_number, type_)
Values ('2', 'end' );
Insert Into library.devices (device_number, type_)
Values ('3', 'end' );
Insert Into library.devices (device_number, type_)
Values ('4', 'mon' );

CREATE TABLE p_id.loops (
monitor integer
);

Create Table p_id.association
(
devices_id integer
) ;

create or replace function loop_association() returns trigger as $$
begin
if new.device_number = library.devices.device_number
and library.devices.type_ = 'end'
then
insert into p_id.association (devices_id) values (new.devices_id );
elseif
new.device_number = library.devices.device_number
and library.devices.type_ = 'mon'
then
insert into p_id.loops (monitor) values (new.devices_id ) ;
end if ;
return null ;
end ;
$$ language plpgsql ;

create trigger loop after insert on p_id.devices
for each row execute procedure loop_association();

CREATE TABLE p_id.settings (
monitor integer
);

CREATE TABLE p_id.alarms (
monitor integer
);