Re: psql can't subtract

On Fri, Mar 25, 2011 at 09:29:57AM -0600, Rob Sargent wrote:
> Running 9.0.3 (client and server)
>
>
> Seems I cannot subtract 1 from the result of position.
>
> select distinct
>        substring( substring(xml_text,1,300),
>                   position( 'xmlns=' in substring(xml_text,1,300)) +
> length('xmlns="'),
>                   position( '"' in (substring(substring(xml_text,1,300),
>                                                position( 'xmlns=' in
> substring(xml_text,1,300)) + length('xmlns="'),
>                                                100)))
>                   ) as namespace
> from elements;

is there any particular reason why you're writing something that
complicated instead of using simple regexp?

select distinct regexp_replace( xml_text, '.*xmlns="([^"]+)".*', E'\\1') from elements;

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/