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@xxxxxxxx>
To: "Bob Pawley" <rjpawley@xxxxxxx>
Cc: "Postgresql" <pgsql-general@xxxxxxxxxxxxxx>
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
