Delivery-Date: Wed, 25 Aug 93 09:11:50 PDT
Resent-From: postman (POSTGRES mailing list)
Resent-Message-Id: <9308251605.AA16910@postgres.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Date: Wed, 25 Aug 93 09:15:40 PDT
From: jean@gso.SAIC.COM (Jean Anderson)
Message-Id: <9308251615.AA04378@ratatosk.css.gov>
To: postgres@postgres.Berkeley.EDU
Subject: Re: unique elements
Resent-To: postgres-dist
Resent-Date: Wed, 25 Aug 93 09:05:27 PDT

jaws@pangaea.dme.nt.gov.au (James Woods 61-89-895257) writes:
> I wish to append attributes from one class into another class on the
> condition that one The value of one of the fields doesn't already exist
> in the target table.  ie I guess I am trying to enforce a uniqueness
> constraint.  The operator != doesn't appear to meet this requirement.

I needed to do a "not exists" query also.  Maybe the following strategy 
will help.

I needed to find just the attributes in a class that were not inherited.

Given these classes from the postgres 'demo' database:

     create person (name = char16, age = int4, location = point)
     create emp (salary = int4, manager = char16) inherits (person)
     create student (gpa = float8) inherits (person)
     create stud_emp (percent = int4) inherits (emp, student)

This query outputs all 'stud_emp' user attributes:

     * retrieve (r.relname, a.attname, t.typname)
     from    a in pg_attribute, r in pg_class, t in pg_type
     where   a.attrelid = r.oid
     and     a.atttypid = t.oid
     and     a.attnum > 0
     and     r.relname ="stud_emp"

     relname       attname       typname
     -----------------------------------
     stud_emp      percent       int4  
     stud_emp      location      point 
     stud_emp      age           int4 
     stud_emp      name          char16
     stud_emp      salary        int4  
     stud_emp      manager       char16
     stud_emp      gpa           float8

All attributes except for 'percent' are inherited from another class.  The 
following user defined function returns how many times an attribute is 
inherited:

     define function does_inherit (language="postquel", returntype=int4) 
          arg is (oid, char16) as 
          "retrieve (total = count 
                {a1.oid from a1 in pg_attribute, a2 in pg_attribute 
                where a1.attrelid = $1 and a1.attname = $2 
                and a1.attname = a2.attname 
                and (a2.attrelid = pg_inherits.inhparent 
                and a1.attrelid = pg_inherits.inhrel) })"

To find all attributes that are NOT inherited, the query is:

     * retrieve (r.relname, a.attname, t.typname)
     from    a in pg_attribute, r in pg_class, t in pg_type
     where   a.attrelid = r.oid
     and     a.atttypid = t.oid
     and     a.attnum > 0
     and     r.relname ="stud_emp"
     and     does_inherit(a.attrelid, a.attname) = 0 

     relname       attname       typname 
     ------------------------------------
     stud_emp      percent       int4    

To find all attributes that ARE inherited, the query is:

     ...
     and     does_inherit(a.attrelid, a.attname) > 0

     relname       attname       typname 
     ------------------------------------
     stud_emp      location      point  
     stud_emp      age           int4  
     stud_emp      name          char16
     stud_emp      salary        int4 
     stud_emp      manager       char16 
     stud_emp      gpa           float8 

Maybe somebody has a simpler was to do the same thing?

 -jean
  jean@gso.saic.com