RE: pl/pgsql - code review + question

I think you need to use syntax:
raise exception ''Member % Not Found'', unitno;

> -----Original Message-----
> From: Gary Stainburn [SMTP:gary(dot)stainburn(at)ringways(dot)co(dot)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(at)curly revcom]$ psql -f t
> DROP
> CREATE
> [revcom(at)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(at)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly