Search Postgresql Archives

Re: Inserting Data

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux