CREATE TABLE families
(
  "FamilyID" integer NOT NULL DEFAULT nextval('"families_FamilyID_seq"'::regclass),
  "FamilyName" character varying( 100 ) NOT NULL,
  CONSTRAINT pk_families PRIMARY KEY ("FamilyID")
) 
WITHOUT OIDS;
ALTER TABLE families OWNER TO postgres;


-- Index: "Index_3"

-- DROP INDEX "Index_3";

CREATE UNIQUE INDEX "Index_3"
  ON families
  USING btree
  ("FamilyName");

CREATE TABLE families
(
  "FamilyID" integer NOT NULL DEFAULT nextval('"families_FamilyID_seq"'::regclass),
  "FamilyValue" character varying( 100 ) NOT NULL,
  CONSTRAINT pk_families PRIMARY KEY ("FamilyID")
) 


CREATE TABLE names
(
  "NameID" integer NOT NULL DEFAULT nextval('"names_NameID_seq"'::regclass),
  "NameValue" character varying( 100 ) NOT NULL,
  CONSTRAINT pk_names PRIMARY KEY ("NameID")
) 

CREATE TABLE fn
(
  "FnID" integer NOT NULL DEFAULT nextval('"fn_fnID_seq"'::regclass),
  "FamilyID" integer,
  "NameID" integer
)

CREATE UNIQUE INDEX "Index_1"
  ON families
  USING btree
  ("FamilyValue");

CREATE UNIQUE INDEX "Index_2"
  ON names
  USING btree
  ("NameValue");

CONSTRAINT fk_fn_ref_families FOREIGN KEY ("FamilyID")
      REFERENCES families ("FamilyID")

CONSTRAINT fk_fn_ref_names FOREIGN KEY ("NameID")
      REFERENCES families ("NameID")

CREATE OR REPLACE FUNCTION "add_fn" (          _Name      varchar,
                                                          _Family      varchar,
)
RETURNS INTEGER AS $$
DECLARE

     Family_ID integer; 
     Name_ID integer; 
    flag integer;
     
BEGIN

     flag =  1 ;
   
     BEGIN
     INSERT INTO families
         ("FamilyValue") VALUES(_Family);
     EXCEPTION WHEN UNIQUE_VIOLATION THEN
              Family_ID = (SELECT "FamilyID" FROM families WHERE "FamilyValue" = _Family);
          flag =  0 ;
       END;
          
     IF flag THEN
          Family_ID = (SELECT currval('"families_FamilyID_seq"'));
     END IF;
     flag =  1 ;

     BEGIN
     INSERT INTO names ("NameValue") VALUES(_Name);
     EXCEPTION WHEN UNIQUE_VIOLATION THEN
              Name_ID = (SELECT "NameID" FROM names WHERE "NameValue" = _Name);
          flag =  0 ;
       END;

     IF flag THEN
          Name_ID = (SELECT currval('"names_NameID_seq"'));
     END IF;
     flag =  1 ;

     BEGIN
     INSERT INTO fn ("FamilyID","NameID") 
          VALUES(Family_ID,Name_ID);

 ....