Re: self defined counter function

-----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-----