pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jeff Eckermann <jeckermann@verio.net>
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 10:46:02 -0500
Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B43@NEZU> (raw)

If the string will always be in that general form, use substring & position
functions (see "String Functions and Operators" in the docs.
Example: unit_number := substr(team_number, strpos(team_number, ''-'') + 1);
If you don't want the leading zero, you could make make the "+1" into "+2".
If you might have more than one leading zero, you could use ltrim:
unit_number := ltrim(unit_number, ''0'');

> -----Original Message-----
> From:	Gary Stainburn [SMTP:gary.stainburn@ringways.co.uk]
> Sent:	Wednesday, July 18, 2001 9:10 AM
> To:	pgsql-sql
> Subject:	pl/pgsql - code review + question
> 
> 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 4: Don't 'kill -9' the postmaster




view thread (14+ messages)  latest in thread

Message-ID: <08CD1781F85AD4118E0800A0C9B8580B094B43@NEZU>
Permalink:  ../08CD1781F85AD4118E0800A0C9B8580B094B43@NEZU/
Also on:    postgresql.org/message-id/08CD1781F85AD4118E0800A0C9B8580B094B43@NEZU

 · 

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: jeckermann@verio.net, gary.stainburn@ringways.co.uk
  Subject: RE: pl/pgsql - code review + question
  In-Reply-To: <08CD1781F85AD4118E0800A0C9B8580B094B43@NEZU>

* 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