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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
