Inserting Data - Mailing list pgsql-general

Michael

OK here goes.

What I am attempting is to break down large categories (devices) into subcategories (monitor, end-devices, pumps, etc) and enter their device_ids into their own tables with, in some cases the device_id as that table’s primary key.

The p_id.devices table holds the dynamic information chosen by the user. The device_number identifies the actual device and is related to the process fluid (fluid_id) that it is measuring or controlling and other features of the device that the user wishes to enter. The user can choose a number of devices such as a monitor (mon), end-device (end), pumps and other gadgets as he sees fit.

The library.devices table holds the static information on each of the devices that are available to the user. At the moment, it holds process control devices that are subdivided as analog or digital, monitor or end-device, and the monitors are further categorized as being used for Level, Pressure, Flow and Temperature for later use.

When the user identifies the device needed the corresponding device_number  is entered into the p_id.devices table.

What I want to do is transfer the device_id (serial) identification of each of the devices entered in the device_number column into different tables.

By comparing the p_id device_number to the library device_number I should be able to identify whether a device is a monitor (mon) or end-device (end).

If it is an end-device I would like the device_id fron the p_id.devices table entered into the p_id.association.devices_id column.

If the device is a monitor I would like the device_id to be entered into the p_id.loops.monitor column. I would also like the monitor device_id to be entered into the Alarms and Settings tables.

CREATE TABLE p_id.devices

(

  p_id_id int4,

  devices_id serial NOT NULL,

  fluid_id int4,

  device_number int4,

  type_ varchar(15),

  mon_function int4,

  controlling int4,

  association int4,

  ps_open varchar(10),

  ps_closed varchar(10)

) ;

CREATE TABLE library.devices

(

  device_number serial NOT NULL,

  item_id varchar NOT NULL,

  device oid,

  a_d varchar(1),

  function_ varchar(15),

  type_ varchar(5)

) ;

CREATE TABLE p_id.loops

(

  p_id_id int4,

  loop_id serial NOT NULL,

  loop_number varchar(15),

  type_ int4,

  a_d varchar(10),

  monitor int4,

  end_device_a int4,

  position_switch_a_1 int4,

  position_switch_a_2 int4,

  end_device_b int4,

  position_switch_b_1 int4,

  position_switch_b_2 int4,

  CONSTRAINT loop_pk PRIMARY KEY (loop_id)

) ;

CREATE TABLE p_id.association

(

  devices_id int4 NOT NULL,

  mon_function int4,

  controlling int4,

  monitor int4,

  CONSTRAINT association_pkey PRIMARY KEY (devices_id)

) ;

CREATE TABLE p_id.alarms

(

  p_id_id int4,

  monitor int4

) ;

CREATE TABLE p_id.settings

(

  p_id_id int4,

  monitor int4,

) ;

The following is my attempt to compare the device_number with the library.devices  to determine the device type. This doesn’t seem to narrow the field down to a single return. If I use INSERT with SELECT I get multiple rows of identical information or, with ‘primary key’ the transaction is rejected.

            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 or replace function loop_association() returns trigger as $$

            begin

            Insert Into p_id.association (devices_id)

            Select new.devices_id

            From p_id.devices

            Where new.device_number = library.devices.device_number

            and library.devices.type_ = 'end' ;

            return null ;

            end ;

            $$ language plpgsql ;

            create trigger loop after insert on p_id.devices

            for each row execute procedure loop_association();

------------------------

Once we get a procedure that works I’ll expand it to include the Alarms and Settings, pumps and other device tables or create another trigger for that function.

There are also other manipulations of data, but what those procedures look like will be determined on what is needed to get this working.

Bob