Subject: unique elements

From: [email-masked] (James Woods 61-89-895257)
Date: Tue, 24 Aug 93 15:41:40 CST

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 guess what i want is something along the lines of an operator that
asks if a particular value already exists in a field of a table, for
example lets call this operator !El for now, which returns true if the
value doesn't already exist.  The way I am wanting to use it is as in:

append target(source.a, ........)
	where source.a !El target.a

Another alternative I have for this is to use it in a rule such as:

define rule up_to_date
	in on append to source
		where new.a !El target.a do
		append target(new.a, ........)

The operator !!= appears to serve this role for int4's (though the
manual says not to use it) and I have seen mention of a notin operator,
but can't work out how to use it.  Specifically the field I will be
doing this with will either be a text or an user defined type depending
on which technique I can use.  Is there a way of doing this??

james Woods
[email-masked]

PS. have I explained my problem clearly or is a followup message required??

---

From: [email-masked] (Jean Anderson)
Date: Wed, 25 Aug 93 09:15:40 PDT

> 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
  [email-masked]