Thread: Inserting Data

Inserting Data

From
Bob Pawley
Date:

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

Re: Inserting Data

From
Michael Fuhr
Date:
On Fri, Aug 25, 2006 at 09:35:21AM -0700, Bob Pawley wrote:
> The library.devices table holds the static information on each
> of the devices that are available to the user.

Is library.devices.device_number a unique attribute?  That is, for
a given device_number, is there at most one row in library.devices?
Or can a given device_number have multiple rows with different
attributes?  If multiple rows then is device_number at least unique
with respect to the type_ column?

> 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 device_number is unique then you could get the device type without
querying library.devices multiple times.  For example:

CREATE OR REPLACE FUNCTION loop_association() RETURNS trigger AS $$
DECLARE
  device_type  varchar;
BEGIN
  SELECT type_ INTO device_type
  FROM library.devices
  WHERE device_number = NEW.device_number;

  IF device_type = 'end' THEN
    INSERT INTO p_id.association (devices_id) VALUES (NEW.devices_id);
  ELSIF device_type = 'mon' THEN
    INSERT INTO p_id.loops (monitor) VALUES (NEW.devices_id);
  END IF;

  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

However, this might not work as written if I understand what you
say here:

> 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.

Are you saying that a query like the following might return more
than one row?

SELECT * FROM library.devices WHERE device_number = 1 AND type_ = 'end';

Or have I misunderstood what you mean by "This doesn't seem to
narrow the field down to a single return"?

--
Michael Fuhr

Re: Inserting Data

From
Bob Pawley
Date:
Michael

This works perfectly. Thanks very much for your help.

What is the reason for redefining type_ as device_type ???

Bob




----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Friday, August 25, 2006 12:31 PM
Subject: Re: [GENERAL] Inserting Data


> On Fri, Aug 25, 2006 at 09:35:21AM -0700, Bob Pawley wrote:
>> The library.devices table holds the static information on each
>> of the devices that are available to the user.
>
> Is library.devices.device_number a unique attribute?  That is, for
> a given device_number, is there at most one row in library.devices?
> Or can a given device_number have multiple rows with different
> attributes?  If multiple rows then is device_number at least unique
> with respect to the type_ column?
>
>> 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 device_number is unique then you could get the device type without
> querying library.devices multiple times.  For example:
>
> CREATE OR REPLACE FUNCTION loop_association() RETURNS trigger AS $$
> DECLARE
>  device_type  varchar;
> BEGIN
>  SELECT type_ INTO device_type
>  FROM library.devices
>  WHERE device_number = NEW.device_number;
>
>  IF device_type = 'end' THEN
>    INSERT INTO p_id.association (devices_id) VALUES (NEW.devices_id);
>  ELSIF device_type = 'mon' THEN
>    INSERT INTO p_id.loops (monitor) VALUES (NEW.devices_id);
>  END IF;
>
>  RETURN NULL;
> END;
> $$ LANGUAGE plpgsql;
>
> However, this might not work as written if I understand what you
> say here:
>
>> 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.
>
> Are you saying that a query like the following might return more
> than one row?
>
> SELECT * FROM library.devices WHERE device_number = 1 AND type_ = 'end';
>
> Or have I misunderstood what you mean by "This doesn't seem to
> narrow the field down to a single return"?
>
> --
> Michael Fuhr

Re: Inserting Data

From
Michael Fuhr
Date:
On Sat, Aug 26, 2006 at 01:22:49PM -0700, Bob Pawley wrote:
> What is the reason for redefining type_ as device_type ???

The function doesn't redefine type_; it selects the value of the
type_ column into a local variable named device_type to avoid
querying library.devices more than once.  That local variable must
have a different name to avoid syntax errors that would result from
the ambiguity of having a variable and a table column with the same
name.

--
Michael Fuhr

Re: Inserting Data

From
Bob Pawley
Date:
Michael

Thanks again for your help.

Bob
----- Original Message -----
From: "Michael Fuhr" <mike@fuhr.org>
To: "Bob Pawley" <rjpawley@shaw.ca>
Cc: "Postgresql" <pgsql-general@postgresql.org>
Sent: Saturday, August 26, 2006 1:36 PM
Subject: Re: [GENERAL] Inserting Data


> On Sat, Aug 26, 2006 at 01:22:49PM -0700, Bob Pawley wrote:
>> What is the reason for redefining type_ as device_type ???
>
> The function doesn't redefine type_; it selects the value of the
> type_ column into a local variable named device_type to avoid
> querying library.devices more than once.  That local variable must
> have a different name to avoid syntax errors that would result from
> the ambiguity of having a variable and a table column with the same
> name.
>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings