Skip site navigation (1) Skip section navigation (2)

Re: self defined counter function

From: "Greg Sabino Mullane" <greg(at)turnstep(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: self defined counter function
Date: 2004-04-21 01:10:48
Message-ID: ab115e11003967cf410778093d5052c4@biglumber.com (view raw or flat)
Thread:
Lists: pgsql-admin
-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200404202107
 
-----BEGIN PGP SIGNATURE-----
 
iD8DBQFAhcpovJuQZxSWSsgRApjkAJ93vJnvVbaK5OyZz/dsia/BE+QcuACg6xJi
YRwBknjImezEs6fxGpdlY20=
=j5K8
-----END PGP SIGNATURE-----



In response to

pgsql-admin by date

Next:From: Christopher Kings-LynneDate: 2004-04-21 01:58:44
Subject: Re: pg_autovacuum crashes when query fails for temp tables
Previous:From: Chris White (cjwhite)Date: 2004-04-20 23:25:01
Subject: Problems doing a restore under 7.4.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group