From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | "pgsql-sql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: pl/pgsql - code review + question |
Date: | 2001-07-18 15:24:10 |
Message-ID: | 0107181624100B.11978@gary.ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Eckermann | 2001-07-18 15:46:02 | RE: pl/pgsql - code review + question |
Previous Message | Richard Huxton | 2001-07-18 15:03:26 | Re: pl/pgsql - code review + question |