Thread: Functional index and string concatenation

    Is there a way to create a "functional index" over a string 
concatenation of two columns ?
   Thanks.

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 ?
> 
>     Thanks.
> 
Like this?

test=# create table strtable (x int, str1 text, str2 text);
CREATE TABLE
test=# create index str_idx on strtable( textcat(str1, str2) );
CREATE INDEX
test=# 

-- 
Edmund Bacon <ebacon@onesystem.com>

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

    Yes, i was searching for the name of equivalent function to "||" 
operator...
   Now, i've got a new problem: I need to concat three values (two 
column values and a sepator constant). But from documentation: " The 
function in the index definition can take more than one argument, but 
they must be table columns, not constants" and "there cannot be 
multicolumn indexes that contain function calls".
   Maybe i should to create a new pgsql function.
                     Thanks !

Edmund Bacon wrote:

>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 ?
>>
>>    Thanks.
>>
>>    
>>
>Like this?
>
>test=# create table strtable (x int, str1 text, str2 text);
>CREATE TABLE
>test=# create index str_idx on strtable( textcat(str1, str2) );
>CREATE INDEX
>test=# 
>
>  
>

On Monday 08 March 2004 16:02, Daniel Henrique Alves Lima wrote:
>     Yes, i was searching for the name of equivalent function to "||"
> operator...
>
>     Now, i've got a new problem: I need to concat three values (two
> column values and a sepator constant). But from documentation: " The
> function in the index definition can take more than one argument, but
> they must be table columns, not constants" and "there cannot be
> multicolumn indexes that contain function calls".
>
>     Maybe i should to create a new pgsql function.

Yes - a wrapper function is the standard solution. I don't think you need to 
do this in 7.4 though.

--  Richard Huxton Archonet Ltd