Thread: creating functional index over multiple columns

hello all,

I am new to this list, and I hope my request is well placed, here. I 
tried searching the archives but I wasn't lucky.

I want to create an index over two columns and one function:

the WHERE clause is:
WHERE lower(name)=? AND type_id=? AND syn_id=?

I know from the docs that I have to create on function that wraps these 
three values for creating the appropriate index. I have practically no 
idea how to do this (e.g. what type/set should this function return?).

Thanks a lot for pointers to documentation/former postings or any help
Cheers
Chantal

hello once more,

sorry, I just found Josh Berkus posting (Re: unique index on function 
and column). I thought (while reading the documentation) it would be 
better to have multicolumn indeces with "multicolumn" constraints. 
Obviously it makes no difference.

Chantal

CREATE INDEX my_idx ON my_table(lower(name), type_id, syn_id); ???

Chris

----- Original Message ----- 
From: "Chantal Ackermann" <chantal.ackermann@biomax.de>
To: <pgsql-sql@postgresql.org>
Sent: Friday, June 28, 2002 7:13 PM
Subject: [SQL] creating functional index over multiple columns


> hello all,
> 
> I am new to this list, and I hope my request is well placed, here. I 
> tried searching the archives but I wasn't lucky.
> 
> I want to create an index over two columns and one function:
> 
> the WHERE clause is:
> WHERE lower(name)=? AND type_id=? AND syn_id=?
> 
> I know from the docs that I have to create on function that wraps these 
> three values for creating the appropriate index. I have practically no 
> idea how to do this (e.g. what type/set should this function return?).
> 
> Thanks a lot for pointers to documentation/former postings or any help
> Cheers
> Chantal
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 
>