Re: 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