Re: Insert

Is this what you need??

Bob

CREATE TABLE p_id.association
(
monitor integer,
devices_id integer NOT NULL,
mon_function integer,
monitoring_fluid integer,
ps_open character varying(5),
ps_closed character varying(5),
CONSTRAINT association_pkey PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.association OWNER TO postgres;

CREATE TABLE p_id.devices
(
p_id_id integer,
devices_id integer NOT NULL DEFAULT
nextval('devices_devices_id_seq'::regclass),
fluid_id integer,
pipe_id integer,
tag_number character varying(100),
device_number integer,
idw_deviceid integer,
sump integer,
CONSTRAINT devices_pk PRIMARY KEY (devices_id)
)
WITHOUT OIDS;
ALTER TABLE p_id.devices OWNER TO postgres;

----- Original Message -----
From: "Adrian Klaver" <aklaver(at)comcast(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Cc: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: [GENERAL] Insert

> On Sunday 23 March 2008 2:40 pm, Bob Pawley wrote:
>> CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
>>
>>
>> Declare
>> xmon_function varchar;
>>
>> Begin
>> Select mon_function into xmon_function
>> From p_id.association
>> Where mon_function = new.mon_function;
>>
>>
>>
>> If xmon_function = p_id.devices.device_number
>> From p_id.association, p_id.devices
>> Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
>> or p_id.association.monitoring_fluid = p_id.devices.pipe_id
>> Then
>>
>> Return Null;
>>
>>
>> Elseif xmon_function = p_id.devices.device_number
>> or xmon_function != p_id.devices.device_number
>> From p_id.association, p_id.devices
>> Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
>> or p_id.association.monitoring_fluid != p_id.devices.pipe_id
>>
>> Then
>> Insert into p_id.devices (device_number) Values (New.mon_function) ;
>>
>> Return Null;
>> END if;
>> End;
>>
>> $$ LANGUAGE plpgsql;
>>
>> create trigger monitorinstall after update on p_id.association
>> for each row execute procedure monitor_install();
>>
>
> Alright I am going to need to see the schema for p_id.association and
> p_id.devices to sort this out. The return 'more than one row' error is
> most
> likely occurring in the IF and ELSEIF clauses. There can only be one value
> on
> each side of the comparison.
>
>>
>>
>>
>>
>>
>>
>> ----- Original Message -----
>> From: "Adrian Klaver" <aklaver(at)comcast(dot)net>
>> To: <pgsql-general(at)postgresql(dot)org>
>> Cc: "Bob Pawley" <rjpawley(at)shaw(dot)ca>
>> Sent: Sunday, March 23, 2008 2:24 PM
>> Subject: Re: [GENERAL] Insert
>>
>> > On Sunday 23 March 2008 1:48 pm, Bob Pawley wrote:
>> >> I have two tables - p_id.association and p_id.devices
>> >>
>> >> If a new row in p_id.association has a value - say 2 in column
>> >> mon_function
>> >> and a value 5 in column monitoring_fluid I want the new value for
>> >> mon_function inserted into table p_id.devices ONLY when 2 AND 5 do not
>> >> appear in the same row in p_id.devices.
>> >>
>> >> The following gives me a return of "more than one row" and I can't
>> >> figure out what's wrong.
>> >
>> > First could you send the actual CREATE FUNCTION statement. I will
>> > assume
>> > you
>> > are using pl/pgsql.
>> > Second I am assuming this is a trigger function, so the CREATE TRIGGER
>> > statement would be useful.
>> >
>> >> Any thoughts would be appreciated.
>> >>
>> >> Bob
>> >>
>> >> Declare
>> >> xmon_function varchar;
>> >>
>> >> Begin
>> >> Select mon_function into xmon_function
>> >> From p_id.association
>> >> Where mon_function = new.mon_function;
>> >
>> > If this is a trigger function, the above is redundant. Just use
>> > new.mon_function.
>> >
>> >> If xmon_function = p_id.devices.device_number
>> >> From p_id.association, p_id.devices
>> >> Where p_id.association.monitoring_fluid = p_id.devices.fluid_id
>> >> or p_id.association.monitoring_fluid = p_id.devices.pipe_id
>> >> Then
>> >
>> > You can't do this. You would need to do something along lines of SELECT
>> > p_id.devices.device_number INTO dev_no FROM ... and
>> > then IF new.mon_function = dev_no THEN
>> >
>> >> Return Null;
>> >>
>> >>
>> >> Elseif xmon_function = p_id.devices.device_number
>> >> or xmon_function != p_id.devices.device_number
>> >> From p_id.association, p_id.devices
>> >> Where p_id.association.monitoring_fluid != p_id.devices.fluid_id
>> >> or p_id.association.monitoring_fluid != p_id.devices.pipe_id
>> >
>> > See above.
>> >
>> >> Then
>> >> Insert into p_id.devices (device_number) Values (New.mon_function) ;
>> >>
>> >> Return Null;
>> >> END if;
>> >> End;
>> >>
>> >>
>> >> -
>> >> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >
>> > --
>> > Adrian Klaver
>> > aklaver(at)comcast(dot)net
>> >
>> > -
>> > Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>>
>> -
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Adrian Klaver
> aklaver(at)comcast(dot)net
>
> -
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general