Thread: psql can't subtract
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;
gives out put as below:
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))) -1
) as namespace
from elements;
gives
ERROR: negative substring length not allowed.
But adding one to position() works, as you can see with the addition of the right angle bracket on some of the lines (there's an added space on the others).
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))) + 1
) as namespace
from elements;
+------------------------------------------------------+
| namespace |
+------------------------------------------------------+
| http://amirsys.com/ns/acres/anatomy/breast-mri/1.0"> |
| http://amirsys.com/ns/acres/anatomy/intro/1.0"> |
| http://amirsys.com/ns/acres/anatomymodule/1.0"> |
| http://amirsys.com/ns/acres/calculator/1.0"> |
....
| http://amirsys.com/ns/acres/casechallenge/1.0"> |
| http://amirsys.com/ns/acres/tsm/1.4"> |
| http://amirsys.com/ns/acres/tsm/1.4" |
| l |
+------------------------------------------------------+
(63 rows)
What I did to solve the issue was change the search string in the last position() to "mlns=", but that of course is an egregious hack...
pg_config gives
BINDIR = /opt/PostgreSQL/9.0.3/bin
DOCDIR = /opt/PostgreSQL/9.0.3/share/doc/postgresql
HTMLDIR = /opt/PostgreSQL/9.0.3/share/doc/postgresql
INCLUDEDIR = /opt/PostgreSQL/9.0.3/include
PKGINCLUDEDIR = /opt/PostgreSQL/9.0.3/include/postgresql
INCLUDEDIR-SERVER = /opt/PostgreSQL/9.0.3/include/postgresql/server
LIBDIR = /opt/PostgreSQL/9.0.3/lib
PKGLIBDIR = /opt/PostgreSQL/9.0.3/lib/postgresql
LOCALEDIR = /opt/PostgreSQL/9.0.3/share/locale
MANDIR = /opt/PostgreSQL/9.0.3/share/man
SHAREDIR = /opt/PostgreSQL/9.0.3/share/postgresql
SYSCONFDIR = /opt/PostgreSQL/9.0.3/etc/postgresql
PGXS = /opt/PostgreSQL/9.0.3/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/opt/PostgreSQL/9.0.3' '--with-openssl' '--with-ldap' '--with-libxml' '--with-ossp-uuid' '--with-pgport=5431' '--with-python'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/9.0.3/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 9.0.3
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;
gives out put as below:
+-----------------------------------------------------+and I want to drop the last char, the double quote mark but
| namespace |
+-----------------------------------------------------+
| |
| http://amirsys.com/ns/acres/anatomy/breast-mri/1.0" |
| http://amirsys.com/ns/acres/anatomy/intro/1.0" |
| http://amirsys.com/ns/acres/anatomymodule/1.0" |
| http://amirsys.com/ns/acres/calculator/1.0" |
...
| http://amirsys.com/ns/acres/table/1.0" |
| http://amirsys.com/ns/acres/tableintro/1.0" |
| http://amirsys.com/ns/acres/tsm/1.4" |
+-----------------------------------------------------+
(41 rows)
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))) -1
) as namespace
from elements;
gives
ERROR: negative substring length not allowed.
But adding one to position() works, as you can see with the addition of the right angle bracket on some of the lines (there's an added space on the others).
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))) + 1
) as namespace
from elements;
+------------------------------------------------------+
| namespace |
+------------------------------------------------------+
| http://amirsys.com/ns/acres/anatomy/breast-mri/1.0"> |
| http://amirsys.com/ns/acres/anatomy/intro/1.0"> |
| http://amirsys.com/ns/acres/anatomymodule/1.0"> |
| http://amirsys.com/ns/acres/calculator/1.0"> |
....
| http://amirsys.com/ns/acres/casechallenge/1.0"> |
| http://amirsys.com/ns/acres/tsm/1.4"> |
| http://amirsys.com/ns/acres/tsm/1.4" |
| l |
+------------------------------------------------------+
(63 rows)
What I did to solve the issue was change the search string in the last position() to "mlns=", but that of course is an egregious hack...
pg_config gives
BINDIR = /opt/PostgreSQL/9.0.3/bin
DOCDIR = /opt/PostgreSQL/9.0.3/share/doc/postgresql
HTMLDIR = /opt/PostgreSQL/9.0.3/share/doc/postgresql
INCLUDEDIR = /opt/PostgreSQL/9.0.3/include
PKGINCLUDEDIR = /opt/PostgreSQL/9.0.3/include/postgresql
INCLUDEDIR-SERVER = /opt/PostgreSQL/9.0.3/include/postgresql/server
LIBDIR = /opt/PostgreSQL/9.0.3/lib
PKGLIBDIR = /opt/PostgreSQL/9.0.3/lib/postgresql
LOCALEDIR = /opt/PostgreSQL/9.0.3/share/locale
MANDIR = /opt/PostgreSQL/9.0.3/share/man
SHAREDIR = /opt/PostgreSQL/9.0.3/share/postgresql
SYSCONFDIR = /opt/PostgreSQL/9.0.3/etc/postgresql
PGXS = /opt/PostgreSQL/9.0.3/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/opt/PostgreSQL/9.0.3' '--with-openssl' '--with-ldap' '--with-libxml' '--with-ossp-uuid' '--with-pgport=5431' '--with-python'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL = -fpic
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/opt/PostgreSQL/9.0.3/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgport -lxml2 -lssl -lcrypto -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 9.0.3
On 25/03/11 15:29, Rob Sargent wrote:
> Running 9.0.3 (client and server)
> select distinct
> substring(
...
> position( 'xmlns=' in substring(xml_text,1,300)) + length('xmlns="'),
> 100))) -1
...
> gives
> ERROR: negative substring length not allowed.
If there is no match the initial position will be zero. Minus one will
give you a negative substring length.
--
Richard Huxton
Archonet Ltd
On 03/25/2011 09:57 AM, Richard Huxton wrote:
> On 25/03/11 15:29, Rob Sargent wrote:
>> Running 9.0.3 (client and server)
>
>> select distinct
>> substring(
> ...
>> position( 'xmlns=' in substring(xml_text,1,300)) + length('xmlns="'),
>> 100))) -1
> ...
>> gives
>> ERROR: negative substring length not allowed.
>
> If there is no match the initial position will be zero. Minus one will
> give you a negative substring length.
>
Of course. One lousy malformed input record. The speed at which the
failure occurred lead me to think it was a parsing issue. Turns out the
bogus record was "near the front of the queue".
Thanks
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/
On 03/25/2011 10:59 AM, hubert depesz lubaczewski wrote:
> 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
>
Just gross incompetency with regexps. Much thanks.