Re: SQL query

David Goodenough wrote:
> I realise this is not strictly a Postgreslql question, but if the best way to
> solve it involves using PG extensions, such as the PG procedural languages
> I am only going to do this on PG and so I am happy to use them.
> 
> I have an address table, with all the normal fields and a customer name
> field and an address type.  There is a constraint that means that the
> combination of customer and type have to be unique.  Normally the
> only record per customer will be of type 'default', but if for instance
> the customer wants a different billing address I would add in a second
> type='billing' address record.  
> 
> I then want to join this table to another table, say an invoice table,
> and I want to use the billing address if present, otherwise the default
> address.  I do not want to create either two addresses or to put both
> addresses on the invoice.

Not sure whether a schema change is possible for you, but you might want 
  to have two tables -
   addresses (customer_id*, addr_id*, ...)
   addr_usage (customer_id*, addr_type*, addr_id)
Add a custom trigger that ensures for every customer_id there is a valid 
row in addr_usage for each addr_type (sales, billing, shipping etc).

That way you can have any mix of addresses you like, and it's explicit 
which address is for which purpose.
-- 
   Richard Huxton
   Archonet Ltd