I was playing with some unit tests for PostgresDAC recently. And one test case was to check TPSQLDataset.Locate routine which allows partial-string and case-sensitive matching options. So I want to have some test result set with integer and text columns. The simplest for me are numbers in both representations: using digits and using words, e.g.
Since I hadn’t much spare time I used nice VALUES command:
VALUES (1, 'one'), (2, 'two'), (3, 'three');
Considering I needed special names for columns I used more complex query:
SELECT col1, col2::varchar(10) FROM (VALUES (1, 'one'), (2, 'two'), (3, 'three')) AS t(col1, col2);
I did my task well. However I was wondering is there any built-in function in PostgreSQL which may convert integer to such words representation. The answer is: not exactly.
I found some references to cash_words function. But there is no any information (except entry in 7.3 release notes) in the official documents. The use case is quite simple:
SELECT cash_words(gen.i::money) FROM generate_series(1, 10) AS gen(i);
I’m fine with this result set, who needs only numbers may trim values using standard functions.
PS In case someone wants to create his own implementation using C (or plpgsql, who knows), cash_words function may be found in the \src\backend\utils\adt\cash.c file. It uses num_word private routine.