Thread: Query

Query

From
Bob Pawley
Date:
I have a trigger that produces an error "returns more than one row".
 
My intent is to fill one table (library.specification) from another (p_id.specification). The p_id table can have multiple instances of the same fluid but I want the library table to have only one record of each fluid.
 
Any insight into what is happening will be appreciated.
 
Bob Pawley
 
 
 create or replace function library_spec() returns trigger as $$
 begin
 
 Declare
  fluid_type varchar ;
 
 Begin
  Select fluid into fluid_type
 From p_id.specifications
 Where  fluid = new.fluid ;
 
 If fluid_type <> library.specifications.fluid Then
 Insert Into library.specifications (fluid) values (new.fluid_type) ;
 
 Elseif
 fluid_type = library.specifications.fluid Then
 Do Nothing ;
 
 End if ;
 return null ;
 end ;
 end ;
 $$ language plpgsql ;
 
 create trigger libspec after insert on p_id.processes
 for each row execute procedure library_spec();

Re: Query

From
Jeff Davis
Date:
On Fri, 2006-10-13 at 09:42 -0700, Bob Pawley wrote:
> I have a trigger that produces an error "returns more than one row".
>
> My intent is to fill one table (library.specification) from another
> (p_id.specification). The p_id table can have multiple instances of
> the same fluid but I want the library table to have only one record of
> each fluid.
>
> Any insight into what is happening will be appreciated.
>
> Bob Pawley
>
>
>  create or replace function library_spec() returns trigger as $$
>  begin
>
>  Declare
>   fluid_type varchar ;
>
>  Begin
>   Select fluid into fluid_type
>  From p_id.specifications
>  Where  fluid = new.fluid ;

Why not just do fluid_type := new.fluid? I don't understand what that
query is supposed to do. You can't fit multiple records into the
fluid_type variable. This might be the source of your error if there are
multiple records with the same fluid_type in p_id.specifications.

>
>  If fluid_type <> library.specifications.fluid Then
>  Insert Into library.specifications (fluid) values (new.fluid_type) ;
>
>  Elseif
>  fluid_type = library.specifications.fluid Then
>  Do Nothing ;

Why an elseif? I don't understand.

>
>  End if ;
>  return null ;
>  end ;
>  end ;
>  $$ language plpgsql ;
>
>  create trigger libspec after insert on p_id.processes
>  for each row execute procedure library_spec();

Hope this helps.

Regards,
    Jeff Davis