Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id A30F56323FF
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Sep 2009 09:01:11 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia,
	port 10024) with ESMTP id 43237-06 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Tue,  1 Sep 2009 12:01:03 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from mail44.e.nsc.no (mail44.e.nsc.no [193.213.115.44])
	by mail.postgresql.org (Postfix) with ESMTP id 97016632732
	for <pgsql-sql@postgresql.org>; Tue,  1 Sep 2009 09:01:03 -0300 (ADT)
Received: from [192.168.0.15] (062016185042.customer.alfanett.no
	[62.16.185.42]) (authenticated bits=0)
	by mail44.nsc.no (8.14.3/8.14.3) with ESMTP id n81C1066023292
	for <pgsql-sql@postgresql.org>; Tue, 1 Sep 2009 14:01:01 +0200 (MEST)
From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Plpgsql: Assign regular expression match to variable
Date: Tue, 1 Sep 2009 14:01:00 +0200
User-Agent: KMail/1.9.10
X-Face: %iea8o"[SI'kMDS.`hz7lU=?\^lC9J-]CVjo;
	f{4-C:$?uVUEjyN~uYFn%*aH_c3d09U>=?utf-8?q?x=0A=09TXtaoj=7EMV=2E8PgG7hik9=3BcCT2N6+u?=@Y{wH\p3*_f~FH9k,Wi74e[_l%.7b^'zJ)8S'^G<(=?utf-8?q?=3D=0A=09zw8=5CPMC3=27=7BKl9=2EzL*ks!b6wA?=,c~'F`k&Vq|ELDGa|4<sl_DHXENz#~X_1qmPq+W;
	1LY2D9
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200909011401.00231.leif@solumslekt.org>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5
	tests=BAYES_00=-2.599
X-Spam-Level: 
X-Archive-Number: 200909/2
X-Sequence-Number: 33067

In Plpgsql, I've got this problem of how to assign an integer extracted 
from a regex to a variable. My approach so far feels kludgy:

-- extract ^#(\d+) from txt
IF txt SIMILAR TO E'#\\d+%' THEN
    my_int := SUBSTR(SUBSTRING(txt, E'#\\d+'), 2,
        LENGTH(SUBSTRING(txt, E'#\\d+')) -1)::INTEGER;
    -- strip ^#\d+ from text
    my_txt := REGEXP_REPLACE(txt, E'^#\\d+ ', '');
END IF;

What I'd like to do is something like this:

my_int := MATCH(txt, '^#(\d+)')::INTEGER;

which would assign the integer atom (\d+) to my_int.
-- 
Leif Biberg Kristensen | Registered Linux User #338009
Me And My Database: http://solumslekt.org/blog/