By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,695 Members | 1,956 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,695 IT Pros & Developers. It's quick & easy.

Adding Indexes to Functions

P: n/a
Hi,

I have a table, where one of the columns "extradata" is a gob of XML.
I'd like to be able to create an index on this function.. so i tried
this
CREATE INDEX actitemsXML_idx ON act_items
(pgxml_xpath(extradata,'//RequestInfo/refund_id/text()','',''));

And i got this nice little error

ERROR: parser: parse error at or near
"'//RequestInfo/refund_id/text()'" at character 66

I tried escaping the single quotes.. that basically leaves me at a psql
prompt with a ', meaning i need to close my quote, but they're all
escaped.

If anyone has any input for adding index's on functions that have single
quotes in them, that would be great.

Thanks.

Jeff.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Mon, Oct 06, 2003 at 11:15:06 -0300,
Jeff MacDonald <in**@bignose.ca> wrote:
Hi,

I have a table, where one of the columns "extradata" is a gob of XML.
I'd like to be able to create an index on this function.. so i tried
this
CREATE INDEX actitemsXML_idx ON act_items
(pgxml_xpath(extradata,'//RequestInfo/refund_id/text()','',''));


I don't think that you can create indexes on functions that had expressions
or constants for parameter values in 7.3 and earlier. You might want to
try this with the 7.4 beta and see if it works there. There will probably
be a 7.4 release candidate this week, so you may be able to consider
7.4 for production soon.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #2

P: n/a

On Mon, 6 Oct 2003, Jeff MacDonald wrote:
I have a table, where one of the columns "extradata" is a gob of XML.
I'd like to be able to create an index on this function.. so i tried
this

CREATE INDEX actitemsXML_idx ON act_items
(pgxml_xpath(extradata,'//RequestInfo/refund_id/text()','',''));

And i got this nice little error

ERROR: parser: parse error at or near
"'//RequestInfo/refund_id/text()'" at character 66

I tried escaping the single quotes.. that basically leaves me at a psql
prompt with a ', meaning i need to close my quote, but they're all
escaped.

If anyone has any input for adding index's on functions that have single
quotes in them, that would be great.


In 7.3 (and earlier), functional indexes must be defined on a function
over columns in the table. You cannot put fixed parameters in the call.
You can generally hack around this by making a function on only the
columns that calls the function with the constant arguments.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.