Received: from stan.ringways.co.uk ([213.38.87.130]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6IEBsa78235 for ; Wed, 18 Jul 2001 10:11:54 -0400 (EDT) (envelope-from gary.stainburn@ringways.co.uk) Received: from gary.ringways.co.uk ([10.1.1.2]) by stan.ringways.co.uk with smtp (Exim 3.13 #1) id 15MrqN-0001Ld-00 for pgsql-sql@postgresql.org; Wed, 18 Jul 2001 14:58:27 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: "pgsql-sql" Subject: pl/pgsql - code review + question Date: Wed, 18 Jul 2001 15:10:23 +0100 X-Mailer: KMail [version 1.2] MIME-Version: 1.0 Message-Id: <01071815102309.11978@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/245 X-Sequence-Number: 4041 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