From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | Jeff Eckermann <jeckermann(at)verio(dot)net>, pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: pl/pgsql - code review + question |
Date: | 2001-07-18 15:56:19 |
Message-ID: | 0107181656190F.11978@gary.ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
--
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2001-07-18 16:24:27 | Re: pl/pgsql - code review + question |
Previous Message | Jeff Eckermann | 2001-07-18 15:48:59 | RE: pl/pgsql - code review + question |