From: | Gary Stainburn <gary(dot)stainburn(at)ringways(dot)co(dot)uk> |
---|---|
To: | "pgsql-sql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | pl/pgsql - code review + question |
Date: | 2001-07-18 14:10:23 |
Message-ID: | 01071815102309.11978@gary.ringways.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 | Richard Huxton | 2001-07-18 15:03:26 | Re: pl/pgsql - code review + question |
Previous Message | Raymond Chui | 2001-07-18 12:18:31 | What is the syntax turn off auto commit? |