Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199402010701.XAA00581@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
Message-Id: <199402010701.XAA16652@faerie.CS.Berkeley.EDU>
From: aoki@postgres.Berkeley.EDU (Paul M. Aoki)
To: shlam@ie.cuhk.hk
Cc: postgres@postgres.Berkeley.EDU
Subject: Re: How to define compound index 
In-reply-to: Your message of Tue, 01 Feb 94 14:36:38 +0800 
	     <9402010636.AA20007@eng.ie.cuhk.hk> 
Date: Mon, 31 Jan 1994 23:01:10 -0800
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Mon, 31 Jan 94 23:01:42 -0800
Resent-XMts: smtp

shlam@ie.cuhk.hk writes:
> Is it possible to define compound index in postgres 4.1?  I will very
> appreciate it if anyone can inform me how to define it. Thanks.

you can't do real multikey indices, but there's a hack around that
using functional indices.  you have to really, really want to do 
it, though.  (you'll see what i mean in a second.)

postgres supports the notion of indices on functional values computed
from a set of attributes.  for example, one of the system catalog 
indices is essentially defined as
	define index <foo> on pg_class 
		using btree (mkoidchar16(oid, relname) oidchar16_ops)
to support this, the standard method for defining a new data type
and operator classes must be followed.  in this case, the function
mkoidchar16 must be defined to form the composite type from the two
component types, and a set of comparison functions (to do <, >, =, 
etc.) must be defined as well.  the user manual describes the rest of 
the procedure -- defining the new operator class, the new operators,
etc. etc.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  aoki@postgres.Berkeley.EDU
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki