Обсуждение: Adding Indexes to Functions
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.
On Mon, Oct 06, 2003 at 11:15:06 -0300, Jeff MacDonald <info@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.
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.