Re: abuse of inheritance?

On Wed, 2002-07-10 at 15:46, Alex Rice wrote:
> Should I be using table inheritance in this scenario?
>
> Table "contact" has name, address, phone, email, etc. columns.
> Table "staff" inherits from "contact because it has many columns in
> common with "contact".
>
> But I want to query staff and get a list of staff-- and omit non-staff
> contacts. The ONLY directive only works down the inheritance tree, so I
> would have to do (something like) this to get a list of staff only:
>
> SELECT staff.whatever
> FROM staff, pg_class
> WHERE staff.tableoid = pg_class.oid AND pg_class.relname = 'staff'
>
> This strikes me as kinda funny and maybe I should not have inherited
> staff from contact to begin with? Not really up to speed on the whole
> object-relational concept yet. :-)

I don't understand what you're wanting here - is there a misprint
above?  What you have done seems to be fine.

Assuming this structure:

     contacts
         |
        staff

If you want a list of staff:

  SELECT * FROM staff

If you want a list of non-staff:

  SELECT * FROM ONLY contacts

If you want everyone:

  SELECT * FROM contacts


If the structure is

     contacts
         |
         +-------------------+-----------------+
         |                   |                 |
        staff               salesmen          bureaucrats

getting a list of non-staff needs a union:

  SELECT col1, col2 FROM salesmen
  UNION
  SELECT col1, col2 FROM bureaucrats

or possibly an EXCEPT, if there are a lot of inherited tables:

   SELECT col1, col2 FROM contacts
   EXCEPT
   SELECT col1, col2 FROM staff