Re: Insert - Mailing list pgsql-general

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