Inserting Data

[Bob Pawley <rjpawley@xxxxxxx>]

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
);