Subject: How to define compound index

From: [email-masked]
Date: Tue, 01 Feb 94 14:36:38 +0800

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.

Alan S. H. Lam
Department of Information Engineering, CUHK, Hong Kong
E-mail: [email-masked] 
Tel: (852) 609 8364 Fax: (852) 603 5032

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 31 Jan 1994 23:01:10 -0800

> 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  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 31 Jan 1994 23:01:10 -0800