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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
aklaver(at)comcast(dot)net