Skip site navigation (1) Skip section navigation (2)

Re: solution found!

From: Torio Dante <torio(at)sb-roscoff(dot)fr>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: solution found!
Date: 2009-02-23 09:44:42
Message-ID: 49A2700A.7060904@sb-roscoff.fr (view raw or flat)
Thread:
Lists: pgsql-novice
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(at)sb-roscoff(dot)fr
http://www.sb-roscoff.fr


pgsql-novice by date

Next:From: JORGE MALDONADODate: 2009-02-23 22:56:21
Subject: PARAMETERS IN QUERIES
Previous:From: Olivier ThauvinDate: 2009-02-23 09:02:54
Subject: Re: where are source rpms

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group