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
