Skip site navigation (1) Skip section navigation (2)

Re: proposal casting from XML[] to int[], numeric[], text[]

From: "Nikolay Samokhvalov" <samokhvalov(at)gmail(dot)com>
To: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: proposal casting from XML[] to int[], numeric[], text[]
Date: 2007-11-11 20:06:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Sep 25, 2007 10:57 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

> Hello
> Current result from xpath function isn't indexable. It cannot be
> problem with possibility cast it to some base types.
> CREATE OR REPLACE FUNCTION xml_list_to_int(xml[])
> RETURNS int[] AS $$
> SELECT ARRAY(SELECT to_number(($1[i])::text,'999999.99')::int
>                FROM generate_series(1, array_upper($1,1)) g(i))
> CREATE CAST (xml[] AS int[]) WITH FUNCTION xml_list_to_int(xml[]);
> -- now I can build functional index
> GIN((xpath('//id/text()',objednavka_v_xml)::int[]));
> Does anybody know better solution?

Alternative (and maybe better) approach would be:
 - create comparison functions that work in the same way as string
comparison functions do (currently, it's straight forward since XML is
stored as string);
 - do NOT create comparison operators to avoid explicit comparing XML values
(to follow standard ways to work with XML and to avoid possible unexpected
 - create opclass based on these functions and, therefore, obtain GIN
indexes support for xml[];
 - describe in the docs, that one can use GIN indexes over XPath
expressions, but should be aware that comparison with non-trivial XML
constants have to be used carefully because of possible problems with
whitespaces, etc (in other words, comparison here is doing letter by letter,
as for varchar).

If there are no objections I'll send patch for this.

In response to


pgsql-hackers by date

Next:From: Pavel StehuleDate: 2007-11-11 20:33:08
Subject: Re: proposal casting from XML[] to int[], numeric[], text[]
Previous:From: David FetterDate: 2007-11-11 19:35:40
Subject: Re: [hibernate-team] PostgreSQLDialect

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group