X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (neptune.hub.org [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id D91B1D1B529
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 21 Nov 2003 07:14:12 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 10321-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Fri, 21 Nov 2003 03:13:42 -0400 (AST)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])
	by svr1.postgresql.org (Postfix) with ESMTP id DDFBDD1B54F
	for <pgsql-sql@postgresql.org>; Fri, 21 Nov 2003 03:13:40 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id hAL7De19009969;
	Fri, 21 Nov 2003 02:13:40 -0500 (EST)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Jim Drummey <drumeng@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: Substrings by Regular Expression 
In-reply-to: <20031119124031.G91425@megazone.bigpanda.com> 
References: <463bd40b.0311140902.27de95a5@posting.google.com>
	<20031119124031.G91425@megazone.bigpanda.com>
Comments: In-reply-to Stephan Szabo <sszabo@megazone.bigpanda.com>
	message dated "Wed, 19 Nov 2003 12:42:31 -0800"
Date: Fri, 21 Nov 2003 02:13:40 -0500
Message-ID: <9968.1069398820@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200311/256
X-Sequence-Number: 16071

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Fri, 14 Nov 2003, Jim Drummey wrote:
>> pds=>  select substring( str1 from ',' ) from address;
>> ERROR:  pg_atoi: error in ",": can't parse ","
>> 
>> Am I missing something?

> That functionality was added in 7.3 I believe.  You may wish
> to upgrade.

Also, it's worth noting that AFAICT the above is illegal per SQL99
--- the textual SUBSTRING variants the spec defines are

         <character substring function> ::=
              SUBSTRING <left paren> <character value expression> FROM <start position>
                          [ FOR <string length> ] <right paren>

         <regular expression substring function> ::=
              SUBSTRING <left paren> <character value expression> FROM
                          <character value expression> FOR
                          <escape character> <right paren>

so it looks to me like you can't omit "FOR <escape>" if you want to
adhere to the letter of the spec.  We do allow omitting that clause
as a pretty-obvious extension ... but it's important to realize that
it *is* an extension.

			regards, tom lane