RE: pl/pgsql - code review + question - Mailing list pgsql-sql

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