The count procedure appears to be working well.
Thanks Adrian for your help.
Bob
----- Original Message -----
From: "Adrian Klaver" <aklaver@xxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>
Sent: Sunday, March 23, 2008 4:04 PM
Subject: Re: Insert
On Sunday 23 March 2008 3:04 pm, Bob Pawley wrote:
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;
Going off what I could deduce I came up with this. It needs to be proofread
and is not tested.
CREATE OR REPLACE FUNCTION monitor_install() RETURNS trigger AS $$
DECLARE
dev_ct integer;
BEGIN
SELECT count(*)INTO dev_ct FROM p_id_p_id.devices WHERE new.mon_function =
p_id.devices.device_number AND
(p_id.association.monitoring_fluid = p_id.devices.fluid_id OR
p_id.association.monitoring_fluid = p_id.devices.pipe_id)
IF dev_ct > 0 THEN
RETURN NULL;
ELSE
INSERT INTO p_id.devices (device_number) VALUES (New.mon_function) ;
END IF;
END;
$$ LANGUAGE plpgsql;
create trigger monitorinstall after update on p_id.association
for each row execute procedure monitor_install();
----- Original Message -----
From: "Adrian Klaver" <aklaver@xxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: 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@xxxxxxxxxxx>
>> To: <pgsql-general@xxxxxxxxxxxxxx>
>> Cc: "Bob Pawley" <rjpawley@xxxxxxx>
>> Sent: Sunday, March 23, 2008 2:24 PM
>> Subject: Re: 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@xxxxxxxxxxxxxx)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >
>> > --
>> > Adrian Klaver
>> > aklaver@xxxxxxxxxxx
>> >
>> > -
>> > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>>
>> -
>> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Adrian Klaver
> aklaver@xxxxxxxxxxx
>
> -
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
aklaver@xxxxxxxxxxx
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
