Thread: self defined counter function

self defined counter function

From
Enrico Ortmann
Date:
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




Re: self defined counter function

From
"Paul Breen"
Date:
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

=========================================================

Re: self defined counter function

From
Enrico Ortmann
Date:
Hi Paul,

PB>   select code from codes where code_urn = (select nextval('code_urn_seq'));

Yes this is a great idea, but it only manages
one character. But I need a counter with
at least a variable length and it has to be
filled with leading zero-values, so that
the length of the generated counter value
is everytime I call the function the same.

Enrico Ortmann

----------------------------------------



Re: self defined counter function

From
Harald Fuchs
Date:
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.

Re: self defined counter function

From
"Greg Sabino Mullane"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


(1679616 is 36^4, 36 is 0-9+A-Z)

CREATE SEQUENCE abase MINVALUE 0 MAXVALUE 1679616 CYCLE;

CREATE OR REPLACE FUNCTION fillbase(BIGINT,INT,INT) RETURNS VARCHAR AS '
DECLARE

mynum    INTEGER;
mybase   ALIAS FOR $2;
myplaces SMALLINT;
first    INTEGER;
divvy    BIGINT;
pop      VARCHAR := \'\';

BEGIN

mynum := $1;
myplaces := $3;

- -- Max we can do
SELECT pow(mybase,myplaces)-1 INTO divvy;
IF (mynum > divvy) THEN
  RAISE EXCEPTION \'The maximum number for base % and % digits is %\',
    mybase,myplaces,divvy;
END IF;

LOOP
 myplaces := myplaces - 1;
 EXIT WHEN myplaces < 0;
 SELECT pow(mybase,myplaces) INTO divvy;
 SELECT mynum/divvy INTO first;
 SELECT pop || CASE WHEN first < 10 THEN first::text ELSE CHR(55+first) END INTO pop;
 SELECT mynum%divvy INTO mynum;
END LOOP;

RETURN pop;
END;
' LANGUAGE PLPGSQL IMMUTABLE;

CREATE OR REPLACE FUNCTION fourbase(BIGINT) RETURNS VARCHAR AS '
SELECT fillbase($1,36,4);
' LANGUAGE SQL;

SELECT fillbase(1234,16,4);
SELECT fillbase(1234,36,4);
SELECT fourbase(123);
SELECT fourbase(nextval('abase'));

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200404202107

-----BEGIN PGP SIGNATURE-----

iD8DBQFAhcpovJuQZxSWSsgRApjkAJ93vJnvVbaK5OyZz/dsia/BE+QcuACg6xJi
YRwBknjImezEs6fxGpdlY20=
=j5K8
-----END PGP SIGNATURE-----