Home > mailing lists

Thread: Syntax problem with INDEX on expression

Syntax problem with INDEX on expression

From

Mike Blackwell

Date:

06 April 2013, 00:03:43

The following expression works fine in a query:

sp_dev=# select (xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text from test;

xpath

----------------------

0000000125846

0000000125843

0000000125844

0000000125842

0000000125845

0000000125847

(10 rows)

However, when the same expression doesn't seem to work for creating an index:

sp_dev=# create index test_idx on test ((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text);

ERROR: syntax error at or near "["

LINE 1: ...udh,array[array['rms','http://www.example.com']]))[1]::text)...

It looks like it doesn't like the array subscript. What might I be missing?

__________________________________________________________________________________
Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley
1750 Wallace Ave | St Charles, IL 60174-3401
Office: 630.313.7818
Mike.Blackwell@rrd.com
http://www.rrdonnelley.com

Re: Syntax problem with INDEX on expression

From

Kevin Grittner

Date:

06 April 2013, 00:18:41

Mike Blackwell <mike.blackwell@rrd.com> wrote:

> sp_dev=# create index test_idx on test
((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text);
> ERROR:  syntax error at or near "["
> LINE 1: ...udh,array[array['rms','http://www.example.com']]))[1]::text)...
>                                                              ^
> It looks like it doesn't like the array subscript.  What might I be missing?

You're missing a set of parentheses around the entire expression.

create index test_idx on test
(((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text));

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Re: Syntax problem with INDEX on expression

From

Mike Blackwell

Date:

06 April 2013, 00:20:17

So, one set to mark the parameter and one for the expression? It's starting to look like Lisp. ^_^

Thanks!

On Fri, Apr 5, 2013 at 4:18 PM, Kevin Grittner <kgrittn@ymail.com> wrote:

Mike Blackwell <mike.blackwell@rrd.com> wrote:

> sp_dev=# create index test_idx on test ((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text);
> ERROR: syntax error at or near "["
> LINE 1: ...udh,array[array['rms','http://www.example.com']]))[1]::text)...
> ^
> It looks like it doesn't like the array subscript. What might I be missing?

You're missing a set of parentheses around the entire expression.

create index test_idx on test (((xpath('//rms:xsid/text()',udh,array[array['rms','http://www.example.com']]))[1]::text));

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company