Обсуждение: Query
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
Declare
fluid_type varchar ;
Begin
Select fluid into fluid_type
From p_id.specifications
Where fluid = new.fluid ;
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) ;
Insert Into library.specifications (fluid) values (new.fluid_type) ;
Elseif
fluid_type = library.specifications.fluid Then
Do Nothing ;
fluid_type = library.specifications.fluid Then
Do Nothing ;
End if ;
return null ;
end ;
end ;
$$ language plpgsql ;
return null ;
end ;
end ;
$$ language plpgsql ;
create trigger libspec after insert on p_id.processes
for each row execute procedure library_spec();
for each row execute procedure library_spec();
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