Re: Insert - Mailing list pgsql-general

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