Postgres Pro
Downloads
Home   >   mailing lists

psql can't subtract - Mailing list pgsql-general

From Rob Sargent
Subject psql can't subtract
Date
Msg-id 4D8CB4F5.4010302@gmail.com
Whole thread Raw
Responses Re: psql can't subtract
Re: psql can't subtract
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: "David Johnston"
Date:
Subject: Re: Need help for constructing query
Next
From: Richard Huxton
Date:
Subject: Re: psql can't subtract