Re: solution found!

Hi All,

I have found the solution to my own problem i posted earlier. Here is
the script if anyone is interested
--Heirarchical data foreign key problem
--Always aligns foreign key(family_id) in the species table via the
genus table
--ensures correspondence and avoids retyping the family_id in the
species table

CREATE OR REPLACE FUNCTION align_family_id() RETURNS TRIGGER AS
$$
DECLARE
    -- a pseudo-variable to recieve the result of the query
    id_family integer;

BEGIN
    IF TG_OP = 'INSERT' THEN
        IF NEW.genus_id IS NOT NULL THEN
            --(back)query statement to select the family id in the genus
table
            --corresponding to the new record(species) on the species table
            --belonging to a particular genus
            SELECT genus.family_id INTO id_family FROM genus, species
            WHERE species.genus_id = genus.genus_id
            AND species.genus_id = NEW.genus_id
            AND species.species_id = NEW.species_id;
            --updates the new species' family_id using the result of the
backquery
            --saved in id_family pseudo-variable
            UPDATE species SET family_id = id_family WHERE species_id =
NEW.species_id;
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE TRIGGER align_family_id
AFTER update OR insert
ON species
FOR EACH ROW
EXECUTE PROCEDURE align_family_id();

--
Dante D. Torio
Geospatial Ecologist
Service Mer et Observation
Station Biologique de Roscoff
place Georges Teissier
BP 74
29682 ROSCOFF cedex Tel. 02.98.29.23.78
Email: torio@sb-roscoff.fr
http://www.sb-roscoff.fr