Re: Adding Indexes to Functions

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.