Thread: Inserting Data
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
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
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
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
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