Postgres Pro
Downloads
Home   >   mailing lists

Thread: Looking for a function

Looking for a function

From
Rich Shepard
Date:
  Is there a function that translates a numeric dollar amount into words for
printing on a check? Long ago and far away, I had such a routine for
Paradox/DOS so I wonder if such a utility (preferably in C) exits for use
with a postgres app.

Thanks,

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com

Re: Looking for a function

From
Bruce Momjian
Date:
Rich Shepard wrote:
>   Is there a function that translates a numeric dollar amount into words for
> printing on a check? Long ago and far away, I had such a routine for
> Paradox/DOS so I wonder if such a utility (preferably in C) exits for use
> with a postgres app.

Yes, there is, but it is part of a data type, MONEY, that we will be
converting to numeric some day:

    test=> select cash_words(100::money);
                 cash_words
    ------------------------------------
     One hundred dollars and zero cents
    (1 row)

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Looking for a function

From
"PG Explorer"
Date:
Here is some code:
Sorry its in plpgsql and not in C


/*
LICENSE: Free
DESCTIPTION: Convert a numeric amount into words.
USAGE: select amount_to_words(12345.12);
URL : http://www.pgexplorer.com
GUI Tool for Postgres
*/

CREATE FUNCTION units_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    WHEN 0 THEN ''''
    WHEN 1 THEN '' ONE''
    WHEN 2 THEN '' TWO''
    WHEN 3 THEN '' THREE''
    WHEN 4 THEN '' FOUR''
    WHEN 5 THEN '' FIVE''
    WHEN 6 THEN '' SIX''
    WHEN 7 THEN '' SEVEN''
    WHEN 8 THEN '' EIGHT''
    WHEN 9 THEN '' NINE''
    WHEN 10 THEN '' TEN''
    WHEN 11 THEN '' ELEVEN''
    WHEN 12 THEN '' TWELVE''
    WHEN 13 THEN '' THIRTEEN''
    WHEN 14 THEN '' FOURTEEN''
    WHEN 15 THEN '' FIFTEEN''
    WHEN 16 THEN '' SIXTEEN''
    WHEN 17 THEN '' SEVENTEEN''
    WHEN 18 THEN '' EIGHTEEN''
    WHEN 19 THEN '' NINETEEN''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';


CREATE FUNCTION units_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    WHEN 0 THEN ''''
    WHEN 1 THEN '' ONE''
    WHEN 2 THEN '' TWO''
    WHEN 3 THEN '' THREE''
    WHEN 4 THEN '' FOUR''
    WHEN 5 THEN '' FIVE''
    WHEN 6 THEN '' SIX''
    WHEN 7 THEN '' SEVEN''
    WHEN 8 THEN '' EIGHT''
    WHEN 9 THEN '' NINE''
    WHEN 10 THEN '' TEN''
    WHEN 11 THEN '' ELEVEN''
    WHEN 12 THEN '' TWELVE''
    WHEN 13 THEN '' THIRTEEN''
    WHEN 14 THEN '' FOURTEEN''
    WHEN 15 THEN '' FIFTEEN''
    WHEN 16 THEN '' SIXTEEN''
    WHEN 17 THEN '' SEVENTEEN''
    WHEN 18 THEN '' EIGHTEEN''
    WHEN 19 THEN '' NINETEEN''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';

CREATE FUNCTION amount_to_words(numeric(15,2)) RETURNS varchar(255) AS '
DECLARE
       amount ALIAS FOR $1;
       amount_str varchar(30);
       tri int4;
       bi int4;
       mi int4;
       th int4;
       hu int4;
       de int4;
       return_str varchar(255);
BEGIN
amount_str = to_char(amount, ''000000000000D00'');
tri = substr(amount_str, 0, 3)::int4;
bi = substr(amount_str, 2, 3)::int4;
mi = substr(amount_str, 5, 3)::int4;
th = substr(amount_str, 8, 3)::int4;
hu = substr(amount_str, 11, 3)::int4;
de = substr(amount_str, 15, 2)::int4;

if tri > 0 then
       return_str = num_to_words(tri) || '' TRILLION '';
       else
       return_str = '''';
END IF;

if bi > 0 then
       return_str = return_str || num_to_words(bi) || '' BILLION '';
END IF;
if mi > 0 then
       return_str = return_str || num_to_words(mi) || '' MILLION '';
END IF;
if th > 0 then
       return_str = return_str || num_to_words(th) || '' THOUSAND '';
END IF;
if hu > 0 then
   return_str =  return_str || num_to_words(hu);
END IF;
IF return_str != '''' then
   return_str = return_str ||'' DOLLARS AND '';
   else
       return_str = ''ZERO DOLLARS AND '';
END IF;

if de > 0 then
   return_str = return_str || num_to_words(de);
END IF;
return_str = return_str || '' CENTS'';

RETURN return_str;
END;
'
 LANGUAGE 'plpgsql';


Hope this helps

----- Original Message -----
From: "Rich Shepard" <rshepard@appl-ecosys.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, February 17, 2002 6:52 AM
Subject: [GENERAL] Looking for a function


>   Is there a function that translates a numeric dollar amount into words
for
> printing on a check? Long ago and far away, I had such a routine for
> Paradox/DOS so I wonder if such a utility (preferably in C) exits for use
> with a postgres app.
>
> Thanks,
>
> Rich
>
> Dr. Richard B. Shepard, President
>
>                        Applied Ecosystem Services, Inc. (TM)
>             2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
>  + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) |
rshepard@appl-ecosys.com
>                          http://www.appl-ecosys.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Re: Looking for a function

From
Rich Shepard
Date:
On Sun, 17 Feb 2002, Bruce Momjian wrote:

> Yes, there is, but it is part of a data type, MONEY, that we will be
> converting to numeric some day:
>
>     test=> select cash_words(100::money);
>                  cash_words
>     ------------------------------------
>      One hundred dollars and zero cents
>     (1 row)

Bruce,

  And I thought that the money data type was deprecated because it was
replaced my numeric.

  I suppose the answer to my question is to locate a copy of the money
source code and rummage around it in for more useful goodies. Hmmm-m-m. All
I find when grepping the source tarball I have for 7.2 is PGmoney.java in
the interfaces directory.

  Would the money data type code be available?

Many thanks,

Rich

Re: Looking for a function

From
Tom Lane
Date:
Rich Shepard <rshepard@appl-ecosys.com> writes:
>   Would the money data type code be available?

src/backend/utils/adt/cash.c
src/include/utils/cash.h

            regards, tom lane

Re: Looking for a function

From
Rich Shepard
Date:
On Sun, 17 Feb 2002, Tom Lane wrote:

> src/backend/utils/adt/cash.c
> src/include/utils/cash.h

  Many thanks, Tom! I looked for 'money', not 'cash'. Heck, I'll accept
either variant.

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com

Re: Looking for a function

From
Bruce Momjian
Date:
Rich Shepard wrote:
> On Sun, 17 Feb 2002, Bruce Momjian wrote:
>
> > Yes, there is, but it is part of a data type, MONEY, that we will be
> > converting to numeric some day:
> >
> >     test=> select cash_words(100::money);
> >                  cash_words
> >     ------------------------------------
> >      One hundred dollars and zero cents
> >     (1 row)
>
> Bruce,
>
>   And I thought that the money data type was deprecated because it was
> replaced my numeric.

You could just cast to Money and print that, though money/cash is an
int4 with cents in the first few bits.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Looking for a function

From
Bruce Momjian
Date:
Rich Shepard wrote:
> On Sun, 17 Feb 2002, Tom Lane wrote:
>
> > src/backend/utils/adt/cash.c
> > src/include/utils/cash.h
>
>   Many thanks, Tom! I looked for 'money', not 'cash'. Heck, I'll accept
> either variant.

Yes, the money/cash distinction was a weird case.  All that stuff should
go into numeric soon.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: Looking for a function

From
"PG Explorer"
Date:
Sorry , here is the other function you are missing

/*
LICENSE: Free
DESCTIPTION: Convert a numeric amount into words.
USAGE: select amount_to_words(12345.12);
URL : http://www.pgexplorer.com
GUI Tool for Postgres
*/

CREATE FUNCTION teens_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    WHEN 0 THEN ''''
    WHEN 1 THEN '' TEN''
    WHEN 2 THEN '' TWENTY''
    WHEN 3 THEN '' THIRTY''
    WHEN 4 THEN '' FORTY''
    WHEN 5 THEN '' FIFTY''
    WHEN 6 THEN '' SIXTY''
    WHEN 7 THEN '' SEVENTY''
    WHEN 8 THEN '' EIGHTY''
    WHEN 9 THEN '' NINETY''
    WHEN 10 THEN '' HUNDRED''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';
CREATE FUNCTION units_to_words(int4) RETURNS varchar(20) AS '
DECLARE
    result_str varchar(20);

BEGIN
SELECT INTO result_str
  CASE $1
    WHEN 0 THEN ''''
    WHEN 1 THEN '' ONE''
    WHEN 2 THEN '' TWO''
    WHEN 3 THEN '' THREE''
    WHEN 4 THEN '' FOUR''
    WHEN 5 THEN '' FIVE''
    WHEN 6 THEN '' SIX''
    WHEN 7 THEN '' SEVEN''
    WHEN 8 THEN '' EIGHT''
    WHEN 9 THEN '' NINE''
    WHEN 10 THEN '' TEN''
    WHEN 11 THEN '' ELEVEN''
    WHEN 12 THEN '' TWELVE''
    WHEN 13 THEN '' THIRTEEN''
    WHEN 14 THEN '' FOURTEEN''
    WHEN 15 THEN '' FIFTEEN''
    WHEN 16 THEN '' SIXTEEN''
    WHEN 17 THEN '' SEVENTEEN''
    WHEN 18 THEN '' EIGHTEEN''
    WHEN 19 THEN '' NINETEEN''
END;

RETURN result_str;
END;
'
 LANGUAGE 'plpgsql';

CREATE FUNCTION amount_to_words(numeric(15,2)) RETURNS varchar(255) AS '
DECLARE
       amount ALIAS FOR $1;
       amount_str varchar(30);
       tri int4;
       bi int4;
       mi int4;
       th int4;
       hu int4;
       de int4;
       return_str varchar(255);
BEGIN
amount_str = to_char(amount, ''000000000000D00'');
tri = substr(amount_str, 0, 3)::int4;
bi = substr(amount_str, 2, 3)::int4;
mi = substr(amount_str, 5, 3)::int4;
th = substr(amount_str, 8, 3)::int4;
hu = substr(amount_str, 11, 3)::int4;
de = substr(amount_str, 15, 2)::int4;

if tri > 0 then
       return_str = num_to_words(tri) || '' TRILLION '';
       else
       return_str = '''';
END IF;

if bi > 0 then
       return_str = return_str || num_to_words(bi) || '' BILLION '';
END IF;
if mi > 0 then
       return_str = return_str || num_to_words(mi) || '' MILLION '';
END IF;
if th > 0 then
       return_str = return_str || num_to_words(th) || '' THOUSAND '';
END IF;
if hu > 0 then
   return_str =  return_str || num_to_words(hu);
END IF;
IF return_str != '''' then
   return_str = return_str ||'' DOLLARS AND '';
   else
       return_str = ''ZERO DOLLARS AND '';
END IF;

if de > 0 then
   return_str = return_str || num_to_words(de);
END IF;
return_str = return_str || '' CENTS'';

RETURN return_str;
END;
'
 LANGUAGE 'plpgsql';





----- Original Message -----
From: "Rich Shepard" <rshepard@appl-ecosys.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, February 17, 2002 6:52 AM
Subject: [GENERAL] Looking for a function


>   Is there a function that translates a numeric dollar amount into words
for
> printing on a check? Long ago and far away, I had such a routine for
> Paradox/DOS so I wonder if such a utility (preferably in C) exits for use
> with a postgres app.
>
> Thanks,
>
> Rich
>
> Dr. Richard B. Shepard, President
>
>                        Applied Ecosystem Services, Inc. (TM)
>             2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
>  + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) |
rshepard@appl-ecosys.com
>                          http://www.appl-ecosys.com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

Time difference changed in 7.2

From
"Gurunandan R. Bhat"
Date:
Hi,

    The difference between two times (interval) seems to have undergone a
change from 7.1 to 7.2. For example

'1:00'::time - '23:00'::time = @ 2 hours in 7.1 and,
'1:00'::time - '23:00'::time = @ 22 hours ago in 7.2.

I assume there must be some sound reason for this change, but I have
used time differences at loads of places in my considerably large
application and the whole thing goes (not surprisingly) completely
haywire.

Is there some way I can revert to the old behaviour? Downgrading my
postgres version is not a choice :). IAC, since

'23:00'::time + '1:00'::interval = '1:00'::time,

shouldn't the old behaviour be considered the more consistent one?

Thanks for any advice and help. Its critical that I get my application
up and running as quickly as possible.

Many thanks and regards,
Gurunandan

Re: Looking for a function

From
Rich Shepard
Date:
On Mon, 18 Feb 2002, PG Explorer wrote:

> Sorry , here is the other function you are missing

  Many thanks. The teen replacement for the second units function makes much
more sense. I'm still in the dark, however, by how these functions will be
called. Because I see no parsing of the amount string, I assume that's up to
me to do, then I call the appropriate function for each component. Is this
correct?

Rich

Dr. Richard B. Shepard, President

                       Applied Ecosystem Services, Inc. (TM)
            2404 SW 22nd Street | Troutdale, OR 97060-1247 | U.S.A.
 + 1 503-667-4517 (voice) | + 1 503-667-8863 (fax) | rshepard@appl-ecosys.com
                         http://www.appl-ecosys.com