Re: pl/pgsql - code review + question - Mailing list pgsql-sql
From | Gary Stainburn |
---|---|
Subject | Re: pl/pgsql - code review + question |
Date | |
Msg-id | 0107181656190F.11978@gary.ringways.co.uk Whole thread Raw |
In response to | pl/pgsql - code review + question (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
List | pgsql-sql |
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