Re: Insert - Mailing list pgsql-general

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