pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Richard Huxton <dev@archonet.com>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: pl/pgsql - code review + question
Date: Wed, 18 Jul 2001 16:03:26 +0100
Message-ID: <004001c10f9b$c9bf75c0$1001a8c0@archonet.com> (raw)
References: <01071815102309.11978@gary.ringways.co.uk>

From: "Gary Stainburn" <gary.stainburn@ringways.co.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




view thread (14+ messages)  latest in thread

Message-ID: <004001c10f9b$c9bf75c0$1001a8c0@archonet.com>
Permalink:  ../004001c10f9b$c9bf75c0$1001a8c0@archonet.com/
Also on:    postgresql.org/message-id/004001c10f9b$c9bf75c0$1001a8c0@archonet.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: dev@archonet.com, gary.stainburn@ringways.co.uk
  Subject: Re: pl/pgsql - code review + question
  In-Reply-To: <004001c10f9b$c9bf75c0$1001a8c0@archonet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox