How to index name of an element in xml column in Postgres

Question

I am trying to index name of elements and I keep running into this error

ERROR: set-returning functions are not allowed in index expressions

This is what I have tried so far.

Sample xml:

<book><title>Manual</title><chapter>1</chapter></book>

DDL:

CREATE INDEX test2_element_name_idx
ON test2 USING GIN(xpath('local-name(/*)',unnest(xpath('//book/*', xml_data))));

Is it possible to index on element names? At the end I want to index on all elements that are under <book> (i.e <title> <chapter>)

One of the sample usecase is, I wanna query (with xpath) to learn how many books have title. And I believe that indexing it would make the queries more efficient. Please correct me if I my understanding is incorrect.

You can not index a function call that returns multiple rows. — user330315
– user330315, Commented Nov 2, 2021 at 15:02

Edouard · Accepted Answer · 2021-11-02 16:50:16Z

1

A stated by a_horse_with_no_name, you can't use a function which returns multiple rows for indexing a table. What you can do instead is to build an array with the multiple rows returned by your function. I propose here after a solution that may need to be adjusted because I never used the xml data type and functions (json is better :-) :

CREATE OR REPLACE FUNCTION xml_data_agg(xml_data xml)
RETURNS xml[] LANGUAGE sql AS
$$
  SELECT array(SELECT xpath('local-name(/*)',unnest(xpath('//book/*', xml_data)))) ;
$$ ;

CREATE INDEX test2_element_name_idx
ON test2 USING GIN(xml_data_agg(xml_data));

Then you can use this index in queries where you can put this type of where clause : WHERE xml_data_agg(xml_data) @> array[list_of_the_xlm_elements_to_be_searched]

answered Nov 2, 2021 at 16:50

Edouard

7,1391 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to index name of an element in xml column in Postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related