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
