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 18A0AD1B9A6 for ; Tue, 20 Apr 2004 09:41:47 -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 04129-03 for ; Tue, 20 Apr 2004 09:41:48 -0300 (ADT) Received: from anchor-post-35.mail.demon.net (anchor-post-35.mail.demon.net [194.217.242.85]) by svr1.postgresql.org (Postfix) with ESMTP id 7719AD1B560 for ; Tue, 20 Apr 2004 09:41:44 -0300 (ADT) Received: from no-dns-yet.demon.co.uk ([80.176.179.58] helo=mailserver.computerpark.local) by anchor-post-35.mail.demon.net with esmtp (Exim 3.35 #1) id 1BFuZP-000NdM-0Z; Tue, 20 Apr 2004 13:41:47 +0100 Received: (from root@localhost) by mailserver.computerpark.local (8.12.6/8.12.6/SuSE Linux 0.6) id i3KBfbil021061; Tue, 20 Apr 2004 12:41:37 +0100 Received: from mailserver.computerpark.local (localhost [127.0.0.1]) by mailserver.computerpark.local (8.12.6/8.12.6/SuSE Linux 0.6) with SMTP id i3KBfZ0o021023; Tue, 20 Apr 2004 12:41:35 +0100 Received: from 10.0.0.61 (SquirrelMail authenticated user pbreen) by mailserver.computerpark.local with HTTP; Tue, 20 Apr 2004 12:41:35 +0100 (BST) Message-ID: <57529.10.0.0.61.1082461295.squirrel@mailserver.computerpark.local> In-Reply-To: <1574570229.20040420095643@radax.net> References: <1574570229.20040420095643@radax.net> Date: Tue, 20 Apr 2004 12:41:35 +0100 (BST) Subject: Re: self defined counter function From: "Paul Breen" To: "Enrico Ortmann" Cc: pgsql-admin@postgresql.org Reply-To: pbreen@computerpark.co.uk User-Agent: SquirrelMail/1.4.1 MIME-Version: 1.0 Content-Type: text/plain;charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 Importance: Normal X-scanner: scanned by Inflex 1.0.12.3 - (http://pldaniels.com/inflex/) X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.2 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME X-Spam-Level: * X-Archive-Number: 200404/260 X-Sequence-Number: 13233 Hello Enrico, You could try this. create table codes (code_urn integer, code char(1)); create sequence code_urn_seq minvalue 0 maxvalue 35 cycle; insert into codes values(0,'0'); ... insert into codes values(35,'Z'); Then the following query would give you the incrementing code purely in the db without having to pull the sequence number back into you app. to encode it. select code from codes where code_urn = (select nextval('code_urn_seq')); Hope this helps - Paul > 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. > > > Enrico Ortmann > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Paul M. Breen, Software Engineer - Computer Park Ltd. Tel: (01536) 417155 Email: pbreen@computerpark.co.uk --------------------------------------------------------- This private and confidential e-mail has been sent to you by Computer Park Ltd. If you are not the intended recipient of this e-mail and have received it in error, please notify us via the email address or telephone number below, and then delete it from your mailbox. Email: mailbox@computerpark.co.uk Tel: +44 (0) 1536 417155 Fax: +44 (0) 1536 417566 Head Office: Computer Park Ltd, Broughton Grange, Headlands, Kettering Northamptonshire NN15 6XA Registered in England: 3022961. Registered Office: 6 North Street, Oundle, Peterborough PE8 4AL =========================================================