Re: Inserting Data - Mailing list pgsql-general

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