scary xpath_table behaviour

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: scary xpath_table behaviour
Date: 2011-04-04 19:57:22
Message-ID: 4D9A22A2.1020705@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When using pipe separated xpath statements to fill multiple columns of
output, there is no guarantee of correlation among the columns of the
rows returned.

Using locally built 9.0.3 (--with-libxml)

I'm dealing with an element which has one fixed attribute (@page-layout)
and either (@collection_id and @type) or (@default).

I was hoping for a result set along the lines of

+---+--------+---------------+------+---------+
|id | layout | collection_id | type | default |
+---+--------|---------------+------+---------+
|1 |layout1 | collection1 | t1 | null |
|2 |layout1 | collection1 | t3 | null |
|3 |layout2 | null | null | true |
+---+--------+---------------+------+---------+

but instead I get

+---+--------+---------------+------+---------+
|id | layout | collection_id | type | default |
+---+--------|---------------+------+---------+
|1 |layout1 | collection1 | t1 | true |
|2 |layout1 | collection1 | t3 | null |
|1 |layout2 | null | null | null |
+---+--------+---------------+------+---------+

where all the non-null values from the last xpath are at the top of the
result set (as they are the only values returned for that xpath query).

Here's my actual
select x.* from
xpath_table(
'doc_id',
'xml_text',
'static_docs',
'//*[name(.) = "page-layout"]/@name
| //*[name(.) = "page-layout"]/@collection-id
| //*[name(.) = "page-layout"]/@type
| //*[name(.) = "page-layout"]/@default ',
'doc_id = ''lookups.xml'''
)
as x(doc_id text, chapter_layout text,
collection_id text, doc_type text, defaulted_type text)

NOTE: There is a multiplicity of namespaces in the xml_text field,
hence the "//*[name(.) = something" trick. Is that the real problem?
And how does one namespace the xpath in xpath_table anyway?

Example data in the xml_text column:

<page-layout name="pi-chapter-layout"
collection-id="pi-brain" type="dx"/>
<page-layout name="pi-chapter-layout"
collection-id="pi-gu" type="dx"/>
<page-layout name="pi-chapter-layout"
collection-id="pi-gi" type="dx"/>
<page-layout name="onc-page-layouts"
collection-id="di-oncology" type="tsm"/>
<page-layout name="pain-management-procedure-chapter-layout"
collection-id="pain-management" type="procedure"/>
<page-layout name="pain-management-procedure-chapter-layout"
collection-id="pain-management" type="section-intro"/>
<page-layout name="procedure-chapter-layout"
collection-id="procedures-book" type="procedure"/>
<page-layout name="procedure-chapter-layout"
collection-id="procedures-book" type="section-intro"/>
<page-layout name="pathology-dx-page-layouts"
default="pathology-dx"/>
<page-layout name="pathology-pcf-overview-page-layouts"
default="pcf-overview"/>
<page-layout name="pathology-intro-page-layouts"
default="path-intro"/>
<page-layout name="pathology-intro-page-layouts"
default="specific-factor"/>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Edoardo Serra 2011-04-04 20:18:43 Foreign key and locking problem
Previous Message Jens Wilke 2011-04-04 19:49:24 Re: Autovacuum firing up during my manual vacuum on same table