Re: Xpath Index in PostgreSQL

Figured it out , thanks for all your help ( had missing brackets )

Here is the sql statement

CREATE INDEX xml_index
ON time_series
USING btree
((
(xpath('/AttributeList/Attributes/Attribute/Name/text()',
external_attributes))[1]::text));

Thanks
Chris

On Fri, Mar 5, 2010 at 4:18 PM, Chris Roffler <croffler(at)earthlink(dot)net>wrote:

> Thanks for your fast response
>
> I have the following sql statement now :
>
> CREATE INDEX xml_index
> ON time_series
> USING btree
>
> (xpath('/AttributeList/Attributes/Attribute/Name/text()',external_attributes))[1]::text
>
> And I am getting the following error :
>
> ERROR: syntax error at or near "["
> LINE 6: ...butes/Attribute/Name/text()',external_attributes))[1]::text;
>
> Any idea ?
>
> Thanks
> Chris
>
> ^
>
>
> On Fri, Mar 5, 2010 at 3:22 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Chris Roffler <croffler(at)earthlink(dot)net> writes:
>> > I am trying to setup an index on an xpath expression but the query never
>> > uses the index.
>> > Could someone enlighten me please ?
>>
>> > Here is the setup :
>>
>> > CREATE INDEX xml_index
>> > ON time_series
>> > USING btree
>> > ((xpath('/AttributeList/Attributes/Attribute/Name/text()'::text,
>> > external_attributes)::text[]));
>>
>> > And here is the query :
>>
>> > select id, name
>> > from
>> > time_series
>> > where
>> > (xpath('/AttributeList/Attributes/Attribute/Name/text()',
>> > external_attributes))[1]::text='Attribute100'
>>
>> Doesn't work that way --- subscripting isn't an indexable operation.
>> To make that query fast with a standard index, you'd need the index to
>> be on
>> (xpath('/AttributeList/Attributes/Attribute/Name/text()',
>> external_attributes))[1]::text
>>
>> regards, tom lane
>>
>
>