pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Harald Fuchs <hf320@protecting.net>
To: pgsql-admin@postgresql.org
Subject: Re: self defined counter function
Date: 20 Apr 2004 16:24:39 +0200
Message-ID: <pu8ygqbuc8.fsf@srv.protecting.net> (raw)
References: <1574570229.20040420095643@radax.net>

In article <1574570229.20040420095643@radax.net>,
Enrico Ortmann <pg.admin@radax.net> writes:

> Hi admins,
> I got a conceptual question on creating a stored procedure
> I need for my app. First the description of the problem:

> I need a counter which works with 36 possible values per
> character [0-9 and thereafter A-Z].
> That means if incremented it should return values as follows:
>     0000
>     0001
>     0002
>     ...
>     0009
>     000A
>     000B
>     000C
>     ...
>     000X
>     000Y
>     000Z
>     0010
>     0011
>     0012
>     ...

> and so on.

> My question is if anybody has already implemented such a
> user-defined 'sequence' in PL/PGSql or if anyone has a great
> idea on how to do that. The only solution I see at present is
> to do that in PHP which is used to code the mentioned userland.

> I suggest to solute on doing the following.

>  - I create a sequence on the DBS
>  - I get the next value of this sequence
>  - I convert the next value in PHP in the code I need

> The problem I see on doing this is that I always need to
> convert if I want to get any kind of information about the
> counter. For example it could be that I only want to know
> what the last given value was. Because of the high traffic
> value on the application I have to take care of, that the
> information I read is quite correct at any time. So if I
> have to convert the value of the sequence therefore I need
> a little time. In the meantime the value could have changed
> and my information is worthless.

> I think the best way would be to code a store procedure
> for the problem. Any ideas would be welcome.

You could use an ordinary sequence internally and translate the
sequential values to your representation on output, like that:

  SELECT num / 36 ||
         CASE
         WHEN num % 36 < 10 THEN chr ((num % 36) + ascii ('0'))
         ELSE chr ((num % 36) - 10 + ascii ('A'))
         END

Me thinks this might be the most efficient way to deal with that.




view thread (5+ messages)  latest in thread

Message-ID: <pu8ygqbuc8.fsf@srv.protecting.net>
Permalink:  ../pu8ygqbuc8.fsf@srv.protecting.net/
Also on:    postgresql.org/message-id/pu8ygqbuc8.fsf@srv.protecting.net

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: hf320@protecting.net
  Subject: Re: self defined counter function
  In-Reply-To: <pu8ygqbuc8.fsf@srv.protecting.net>
  References: <1574570229.20040420095643@radax.net> <pu8ygqbuc8.fsf@srv.protecting.net>

* 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