Thread: Insert

Insert

From

Bob Pawley

Date:

23 March 2008, 20:52:30

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;

Re: Insert

From

Adrian Klaver

Date:

23 March 2008, 21:23:32

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

Re: Insert

From

Bob Pawley

Date:

23 March 2008, 21:44:22

 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

Re: Insert

From

Adrian Klaver

Date:

23 March 2008, 21:57:37

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

Re: Insert

From

Bob Pawley

Date:

23 March 2008, 22:08:33

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

Re: Insert

From

Adrian Klaver

Date:

23 March 2008, 22:37:24

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

Re: Insert

From

Adrian Klaver

Date:

23 March 2008, 23:03:40

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

Re: Insert

From

Bob Pawley

Date:

24 March 2008, 16:56:15

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