X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E9D28D1DF0D for ; Tue, 20 Apr 2004 11:24:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 54594-04 for ; Tue, 20 Apr 2004 11:24:42 -0300 (ADT) Received: from main.gmane.org (main.gmane.org [80.91.224.249]) by svr1.postgresql.org (Postfix) with ESMTP id C2A4DD1DDA5 for ; Tue, 20 Apr 2004 11:24:37 -0300 (ADT) Received: from list by main.gmane.org with local (Exim 3.35 #1 (Debian)) id 1BFwAz-0006N7-00 for ; Tue, 20 Apr 2004 16:24:41 +0200 Received: from srv.protecting.net ([212.126.218.242]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 20 Apr 2004 16:24:41 +0200 Received: from hf320 by srv.protecting.net with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Tue, 20 Apr 2004 16:24:41 +0200 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: Harald Fuchs Subject: Re: self defined counter function Date: 20 Apr 2004 16:24:39 +0200 Organization: Linux Private Site Lines: 63 Message-ID: References: <1574570229.20040420095643@radax.net> Reply-To: hf320@protecting.net Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII X-Complaints-To: usenet@sea.gmane.org X-Gmane-NNTP-Posting-Host: srv.protecting.net X-No-Archive: yes User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/20.7 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.0 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200404/266 X-Sequence-Number: 13239 In article <1574570229.20040420095643@radax.net>, Enrico Ortmann 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.