Received: from nezu.gs.verio.net (nezu.gs.verio.net [204.27.65.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6IFkFa15847 for ; Wed, 18 Jul 2001 11:46:15 -0400 (EDT) (envelope-from jeckermann@verio.net) Received: by NEZU with Internet Mail Service (5.5.2650.21) id <3G6FW0Y8>; Wed, 18 Jul 2001 10:46:04 -0500 Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B43@NEZU> From: Jeff Eckermann To: "'Gary Stainburn'" , pgsql-sql Subject: RE: pl/pgsql - code review + question Date: Wed, 18 Jul 2001 10:46:02 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain X-Archive-Number: 200107/250 X-Sequence-Number: 4046 If the string will always be in that general form, use substring & position functions (see "String Functions and Operators" in the docs. Example: unit_number := substr(team_number, strpos(team_number, ''-'') + 1); If you don't want the leading zero, you could make make the "+1" into "+2". If you might have more than one leading zero, you could use ltrim: unit_number := ltrim(unit_number, ''0''); > -----Original Message----- > From: Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk] > Sent: Wednesday, July 18, 2001 9:10 AM > To: pgsql-sql > Subject: pl/pgsql - code review + question > > Hi all, I've just written my first pl/pgsql function (code included below > for > you to pull apart). > > 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. > > TIA Gary > > __BEGIN__ > CREATE FUNCTION getunitno(int4) RETURNS varchar AS ' > DECLARE > mid ALIAS FOR $1; > results RECORD; > BEGIN > select into results t.tnumber as tnumber, m.mnumber as mnumber > from teams t, members m > where t.tid = m.mteam and m.mid = mid; > if results.mnumber < 10 then > return results.tnumber || ''-0'' || results.mnumber; > else > return results.tnumber || ''-'' || results.mnumber; > end if; > END; > ' LANGUAGE 'plpgsql'; > __END__ > > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster