Re: Insert

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