From: | "Richard Huxton" <dev(at)archonet(dot)com> |
---|---|
To: | "Gary Stainburn" <gary(dot)stainburn(at)ringways(dot)co(dot)uk>, "pgsql-sql" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: pl/pgsql - code review + question |
Date: | 2001-07-18 15:03:26 |
Message-ID: | 004001c10f9b$c9bf75c0$1001a8c0@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
From: "Gary Stainburn" <gary(dot)stainburn(at)ringways(dot)co(dot)uk>
> Hi all, I've just written my first pl/pgsql function (code included below
for
> you to pull apart).
Looks fine to me. Try it with "SELECT INTO" etc rather than "select into"
and see if you prefer it - I find it makes the variables/fields stand out
better.
> 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.
Use the substr() function. Since you are careful to turn member-numbers into
2-digits you shouldn't need anything more complex.
richardh=> select substr('abcdefghijkl',2,3);
substr
--------
bcd
(1 row)
So, something like
teamnum := substr(idstring,1,6);
membnum := substr(idstring,7,2);
This can get you your team/member which you can query to get your "mid". If
you aren't enforcing uniqueness on (tnumber,mnumber) now might be a good
time to do so.
If the teamnum isn't always a fixed length search for the '-' with strpos()
richardh=> select strpos('abcdefg','e');
strpos
--------
5
HTH
- Richard Huxton
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2001-07-18 15:24:10 | Re: pl/pgsql - code review + question |
Previous Message | Gary Stainburn | 2001-07-18 14:10:23 | pl/pgsql - code review + question |