Re: Inserting Data

On Fri, Aug 18, 2006 at 09:27:19AM -0700, Bob Pawley wrote:
> I need to insert data from one table into three other tables.
>
> I attempted the following format.
[...]
> insert into p_id.loops (monitor)
> Select p_id.devices.devices_id
> Where p_id.devices.device_number = library.devices.device_number
> and library.devices.type_ = 'mon' ;

Style recommendation: add a FROM clause to these queries. Missing
FROM clauses are nonstandard and can cause unexpected results.
PostgreSQL 8.0 and earlier allow such queries by default but in 8.1
they're disabled by default. See the add_missing_from configuration
setting:

http://www.postgresql.org/docs/8.1/interactive/runtime-config-compatible.html#GUC-ADD-MISSING-FROM

> Unfortunately this gave multiple results on the target tables.

What do you mean by "multiple results"? Do you mean that each row
inserted into p_id.devices causes multiple rows to be inserted into
p_id.loops, p_id.settings, and p_id.alarms? The trigger function's
query doesn't reference the new row that was inserted into p_id.devices;
it joins the entire table against library.devices. Did you mean to
do something like the following?

INSERT INTO p_id.loops (monitor)
SELECT NEW.devices_id
FROM library.devices
WHERE NEW.device_number = library.devices.device_number
AND library.devices.type_ = 'mon';

--
Michael Fuhr