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@xxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>
Sent: Sunday, March 23, 2008 2:58 PM
Subject: Re: 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@xxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Cc: "Bob Pawley" <rjpawley@xxxxxxx>
Sent: Sunday, March 23, 2008 2:24 PM
Subject: Re: 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@xxxxxxxxxxxxxx)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Adrian Klaver
> aklaver@xxxxxxxxxxx
>
> -
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Adrian Klaver
aklaver@xxxxxxxxxxx
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
