Received: from tele-post-20.mail.demon.net (tele-post-20.mail.demon.net [194.217.242.20]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6IFXTa09837 for ; Wed, 18 Jul 2001 11:33:29 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by tele-post-20.mail.demon.net with esmtp (Exim 2.12 #2) id 15MtKD-0008d2-0K; Wed, 18 Jul 2001 15:33:23 +0000 Received: from client (client.archonet.com [192.168.1.16]) by mainbox.archonet.com (Postfix) with SMTP id 11BBB12D18; Wed, 18 Jul 2001 16:10:34 +0100 (BST) Message-ID: <004001c10f9b$c9bf75c0$1001a8c0@archonet.com> From: "Richard Huxton" To: "Gary Stainburn" , "pgsql-sql" References: <01071815102309.11978@gary.ringways.co.uk> Subject: Re: pl/pgsql - code review + question Date: Wed, 18 Jul 2001 16:03:26 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200107/249 X-Sequence-Number: 4045 From: "Gary Stainburn" > Hi all, I've just written my first pl/pgsql function (code included below for > you to pull apart). Looks fine to me. Try it with "SELECT INTO" etc rather than "select into" and see if you prefer it - I find it makes the variables/fields stand out better. > It takes an int4 mid (e.g. 15) and then using a select pulls out the team > number (e.g. 'NE/012' and a unit number (e.g. 2) and returns the full unit > number NE/012-02. > > I now want to write the reverse function, where I can enter 'NE/012-02' and > get back the mid 15. The bit I'm stuck on is now I split the team part from > the member part so that I can build the select statement. Use the substr() function. Since you are careful to turn member-numbers into 2-digits you shouldn't need anything more complex. richardh=> select substr('abcdefghijkl',2,3); substr -------- bcd (1 row) So, something like teamnum := substr(idstring,1,6); membnum := substr(idstring,7,2); This can get you your team/member which you can query to get your "mid". If you aren't enforcing uniqueness on (tnumber,mnumber) now might be a good time to do so. If the teamnum isn't always a fixed length search for the '-' with strpos() richardh=> select strpos('abcdefg','e'); strpos -------- 5 HTH - Richard Huxton