Re: Inserting Data

On Wed, Aug 23, 2006 at 07:34:43PM -0700, Bob Pawley wrote:
> Let me explain.

I'll build a simple example based on what you describe. Please
make corrections as necessary.

> I have a table called p_id.devices which accumulates the devices_id for a
> multitude of differing devices used in P&ID development.(Process
> Engineering)

CREATE TABLE p_id.devices (
devices_id integer
);

> I also have a table called library.devices which is ( or soon will be ) a
> detailed explanation of all of the particular devices available.

CREATE TABLE library.devices (
device_number integer,
type_ text
);

> I accumulate the device_ids of the devices used during the P&ID
> development. What I need to do now is distribute the various devices to
> their own tables (Loops as well as others) based on the information found
> in the library.devices table. I'm trying to make best use of the
> relationship features of a relational data base.

CREATE TABLE p_id.loops (
monitor integer
);

CREATE TABLE p_id.settings (
monitor integer
);

CREATE TABLE p_id.alarms (
monitor integer
);

> However, I am frustrated by what appears to be a restrictive use of simple
> logic. I am sure there is a reason for developing general SQL and
> PostgreSQL in the manner in which it has developed. I am just trying to
> parse the details behind the structure as best I can.

If the above CREATE TABLE statements are correct as far as the
relevant columns are concerned then please post some INSERT statements
that will set up an initial state. If I understand correctly then
that would be some records inserted into library.devices and nothing
(yet) in the other tables.

Once we've established the initial state then we'll consider what
happens next. If I understand then that would be inserts and updates
into p_id.devices. Please show some INSERT and UPDATE statements
and describe what effect those statements should have on p_id.loops,
p_id.settings, and/or p_id.alarms. For example:

INSERT INTO p_id.devices (devices_id) VALUES (1);
-- such-and-such should happen in p_id.loops
-- such-and-such should happen in p_id.settings
-- such-and-such should happen in p_id.alarms

INSERT INTO p_id.devices (devices_id) VALUES (2);
-- such-and-such should happen in p_id.loops
-- such-and-such should happen in p_id.settings
-- such-and-such should happen in p_id.alarms

UPDATE p_id.devices SET column_name = new_value WHERE some_condition;
-- such-and-such should happen in p_id.loops
-- such-and-such should happen in p_id.settings
-- such-and-such should happen in p_id.alarms

Once we have a clear picture of what should happen in response to
what actions then it'll be easier to figure out how to make that
happen.

--
Michael Fuhr