Re: Insert - Mailing list pgsql-general

 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