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(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
>
> -
> 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