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:
+-----------------------------------------------------+
| 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)
and I want to drop the last char, the double quote mark but
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
