X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 92CFED1D1F2
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Mar 2004 15:48:34 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 34241-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  8 Mar 2004 11:48:37 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id A699AD1DB6C
	for <pgsql-sql@postgresql.org>; Mon,  8 Mar 2004 11:48:32 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i28FmZ7K004126;
	Mon, 8 Mar 2004 10:48:35 -0500 (EST)
To: Edmund Bacon <ebacon@onesystem.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Functional index and string concatenation 
In-reply-to: <1078759686.10233.1.camel@elb_lx.onesystem.ca> 
References: <404C8F08.2070403@yahoo.com.br>
	<1078759686.10233.1.camel@elb_lx.onesystem.ca>
Comments: In-reply-to Edmund Bacon <ebacon@onesystem.com>
	message dated "Mon, 08 Mar 2004 08:28:07 -0700"
Date: Mon, 08 Mar 2004 10:48:35 -0500
Message-ID: <4125.1078760915@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200403/58
X-Sequence-Number: 17157

Edmund Bacon <ebacon@onesystem.com> writes:
> On Mon, 2004-03-08 at 08:19, Daniel Henrique Alves Lima wrote:
>> Is there a way to create a "functional index" over a string 
>> concatenation of two columns ?

> test=# create index str_idx on strtable( textcat(str1, str2) );

As of 7.4 you can do it more directly:

create index str_idx on strtable( (str1 || str2) );

The disadvantage of the textcat() locution is that the planner will only
match it up to queries that also say textcat().

			regards, tom lane