From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-admin@postgresql.org
Subject: Re: self defined counter function
Date: Wed, 21 Apr 2004 01:10:48 -0000
Message-ID: <ab115e11003967cf410778093d5052c4@biglumber.com> (raw)
In-Reply-To: <1574570229.20040420095643@radax.net>
-----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-----
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: greg@turnstep.com
Subject: Re: self defined counter function
In-Reply-To: <ab115e11003967cf410778093d5052c4@biglumber.com>
References: <1574570229.20040420095643@radax.net> <ab115e11003967cf410778093d5052c4@biglumber.com>
* 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