Received: from stan.ringways.co.uk ([213.38.87.130]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6IFPga05878 for ; Wed, 18 Jul 2001 11:25:42 -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 15Mszn-0001Wa-00 for pgsql-sql@postgresql.org; Wed, 18 Jul 2001 16:12:15 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: "pgsql-sql" Subject: Re: pl/pgsql - code review + question Date: Wed, 18 Jul 2001 16:24:10 +0100 X-Mailer: KMail [version 1.2] References: <01071815102309.11978@gary.ringways.co.uk> In-Reply-To: <01071815102309.11978@gary.ringways.co.uk> MIME-Version: 1.0 Message-Id: <0107181624100B.11978@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/246 X-Sequence-Number: 4042 Okay, I've been hit round the back of the head, and I realised that the postgresql functions (inc subtring) are available in pl/pgsql, so that's my problem solved. I've written the getmid function as below, which is basically the same as the getunitno I included in my first post. My problem now is that when I include the code to handle the record not being there, from the pgsql chapter (section 23.2.3.3) I get the following errors based of the function below. Can anyone explain why the concat of the string is failing. If I simply "raise exception ''member not found''" all works fine. __BEGIN__ (screen output) [revcom@curly revcom]$ psql -f t DROP CREATE [revcom@curly revcom]$ psql -c "select getmid('NE/011-06');" NOTICE: plpgsql: ERROR during compile of getmid near line 15 ERROR: parse error at or near "|" [revcom@curly revcom]$ __END__ __BEGIN__ (~/t which contains the function def) drop function getmid(varchar); CREATE FUNCTION getmid(varchar) RETURNS int4 AS ' DECLARE unitno ALIAS FOR $1; teamno varchar; munit int4; results RECORD; BEGIN teamno := substring(unitno from 1 for 6); munit := substring(unitno from 8); select into results m.mid as mid from teams t, members m where t.tid = m.mteam and t.tnumber = ''teamno'' and m.mnumber = munit; if not found then raise exception ''Member '' || unitno || '' not found''; return 0; end if; return results.mid; END; ' LANGUAGE 'plpgsql'; __END__ Gary On Wednesday 18 July 2001 3:10 pm, Gary Stainburn wrote: > 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