|
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: +-----------------------------------------------------+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 |
