Received: from stan.ringways.co.uk ([213.38.87.130]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f6IFvta20723 for ; Wed, 18 Jul 2001 11:57:56 -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 15MtUt-0001c9-00; Wed, 18 Jul 2001 16:44:23 +0100 Content-Type: text/plain; charset="iso-8859-1" From: Gary Stainburn Organization: Ringways Garages Ltd To: Jeff Eckermann , pgsql-sql Subject: Re: pl/pgsql - code review + question Date: Wed, 18 Jul 2001 16:56:19 +0100 X-Mailer: KMail [version 1.2] References: <08CD1781F85AD4118E0800A0C9B8580B094B44@NEZU> In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B094B44@NEZU> MIME-Version: 1.0 Message-Id: <0107181656190F.11978@gary.ringways.co.uk> Content-Transfer-Encoding: 8bit X-Archive-Number: 200107/247 X-Sequence-Number: 4043 Hi Jeff, That's sorted my exceptions out, now all I've got to do is find out why it's not finding the record in the first place. Gary. On Wednesday 18 July 2001 4:48 pm, Jeff Eckermann wrote: > I think you need to use syntax: > raise exception ''Member % Not Found'', unitno; > > > -----Original Message----- > > From: Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk] > > Sent: Wednesday, July 18, 2001 10:24 AM > > To: pgsql-sql > > Subject: Re: pl/pgsql - code review + question > > > > 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 > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly -- 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