Обсуждение: Insert
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. 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 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;
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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver aklaver@comcast.net
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(); ----- Original Message ----- From: "Adrian Klaver" <aklaver@comcast.net> To: <pgsql-general@postgresql.org> Cc: "Bob Pawley" <rjpawley@shaw.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@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general > > -- > Adrian Klaver > aklaver@comcast.net > > - > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
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@comcast.net> > To: <pgsql-general@postgresql.org> > Cc: "Bob Pawley" <rjpawley@shaw.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@postgresql.org) > >> To make changes to your subscription: > >> http://www.postgresql.org/mailpref/pgsql-general > > > > -- > > Adrian Klaver > > aklaver@comcast.net > > > > - > > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-general > > - > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- Adrian Klaver aklaver@comcast.net
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@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.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@comcast.net>
>> To: <pgsql-general@postgresql.org>
>> Cc: "Bob Pawley" <rjpawley@shaw.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@postgresql.org)
>> >> To make changes to your subscription:
>> >> http://www.postgresql.org/mailpref/pgsql-general
>> >
>> > --
>> > Adrian Klaver
>> > aklaver@comcast.net
>> >
>> > -
>> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> > To make changes to your subscription:
>> > http://www.postgresql.org/mailpref/pgsql-general
>>
>> -
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> -
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
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;
>
Yes. Some further questions. How are association and devices related? I see
devices_id in both so is one a Foreign Key to the other? I ask because in
your function you relate them via association.mon_fluid=devices.device_number
as well as
p_id.association.monitoring_fluid = p_id.devices.fluid_id
or p_id.association.monitoring_fluid = p_id.devices.pipe_id
I am not quite sure I understand monitoring_fluid=pipe_id.
>
>
>
> ----- Original Message -----
> From: "Adrian Klaver" <aklaver@comcast.net>
> To: <pgsql-general@postgresql.org>
> Cc: "Bob Pawley" <rjpawley@shaw.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@comcast.net>
> >> To: <pgsql-general@postgresql.org>
> >> Cc: "Bob Pawley" <rjpawley@shaw.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@postgresql.org)
> >> >> To make changes to your subscription:
> >> >> http://www.postgresql.org/mailpref/pgsql-general
> >> >
> >> > --
> >> > Adrian Klaver
> >> > aklaver@comcast.net
> >> >
> >> > -
> >> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-general
> >>
> >> -
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> > --
> > Adrian Klaver
> > aklaver@comcast.net
> >
> > -
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> -
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
aklaver@comcast.net
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@comcast.net>
> To: <pgsql-general@postgresql.org>
> Cc: "Bob Pawley" <rjpawley@shaw.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@comcast.net>
> >> To: <pgsql-general@postgresql.org>
> >> Cc: "Bob Pawley" <rjpawley@shaw.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@postgresql.org)
> >> >> To make changes to your subscription:
> >> >> http://www.postgresql.org/mailpref/pgsql-general
> >> >
> >> > --
> >> > Adrian Klaver
> >> > aklaver@comcast.net
> >> >
> >> > -
> >> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-general
> >>
> >> -
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> > --
> > Adrian Klaver
> > aklaver@comcast.net
> >
> > -
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> -
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
aklaver@comcast.net
The count procedure appears to be working well.
Thanks Adrian for your help.
Bob
----- Original Message -----
From: "Adrian Klaver" <aklaver@comcast.net>
To: <pgsql-general@postgresql.org>
Cc: "Bob Pawley" <rjpawley@shaw.ca>
Sent: Sunday, March 23, 2008 4:04 PM
Subject: Re: [GENERAL] 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@comcast.net>
> To: <pgsql-general@postgresql.org>
> Cc: "Bob Pawley" <rjpawley@shaw.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@comcast.net>
> >> To: <pgsql-general@postgresql.org>
> >> Cc: "Bob Pawley" <rjpawley@shaw.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@postgresql.org)
> >> >> To make changes to your subscription:
> >> >> http://www.postgresql.org/mailpref/pgsql-general
> >> >
> >> > --
> >> > Adrian Klaver
> >> > aklaver@comcast.net
> >> >
> >> > -
> >> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> > To make changes to your subscription:
> >> > http://www.postgresql.org/mailpref/pgsql-general
> >>
> >> -
> >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> >> To make changes to your subscription:
> >> http://www.postgresql.org/mailpref/pgsql-general
> >
> > --
> > Adrian Klaver
> > aklaver@comcast.net
> >
> > -
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
>
> -
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
aklaver@comcast.net
-
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general