pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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-----





view thread (5+ messages)

Message-ID: <ab115e11003967cf410778093d5052c4@biglumber.com>
Permalink:  ../ab115e11003967cf410778093d5052c4@biglumber.com/
Also on:    postgresql.org/message-id/ab115e11003967cf410778093d5052c4@biglumber.com

 · 

reply

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