psql can't subtract
| От | Rob Sargent |
|---|---|
| Тема | psql can't subtract |
| Дата | |
| Msg-id | 4D8CB4F5.4010302@gmail.com обсуждение исходный текст |
| Ответы |
Re: psql can't subtract
(Richard Huxton <dev@archonet.com>)
Re: psql can't subtract (hubert depesz lubaczewski <depesz@depesz.com>) |
| Список | pgsql-general |
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
В списке pgsql-general по дате отправления: