Re: pl/pgsql - code review + question - Mailing list pgsql-sql
From | Gary Stainburn |
---|---|
Subject | Re: pl/pgsql - code review + question |
Date | |
Msg-id | 0107181624100B.11978@gary.ringways.co.uk Whole thread Raw |
In response to | pl/pgsql - code review + question (Gary Stainburn <gary.stainburn@ringways.co.uk>) |
Responses |
Re: pl/pgsql - code review + question
(Tom Lane <tgl@sss.pgh.pa.us>)
|
List | pgsql-sql |
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