to_char(), md5() (long)

Hi hackers,

1) I programming to_char() routine (inspire with oracle, and with good
advice from Thomas L.) Current version is on
ftp://ftp2.zf.jcu.cz/users/zakkr/pg/ora_func.tar.gz,

(In future I want implement more (oracle compatible routines (to_date,
to_number.. and more)..)

2) I have comlete imlementation of MD5 routine for PqSQL
(ftp://ftp2.zf.jcu.cz/users/zakkr/pg/md5.tar.gz). As base for source code
is used code from Debian md5sum. In Debian is this code distributed
_without_ some restriction under GPL. Is any problem add this code
(if we want) to PgSQL contrib?

(If it is not problem I can try make more routines based on md5
(aggregate func. - md5_count() ...etc)).

I enclose description for the current version of to_char(). Please, send me
any commets..

Zakkr

------------------------------------------------------------------------------
TO_CHAR(datetime, text)

* now is not supported:
- spelled-out SP suffix
- AM/PM
- ...and not supported number to character converting
TO_CHAR(number, 'format')

* now is supported:

suffixes:
TH ot th - ordinal number
FM - fill mode

HH - hour of day (01-12)
HH12 - -- // --
HH24 - hour (00-24)
MI - minute (00-59)
SS - socond (00-59)
SSSS - seconds past midnight (0-86399)
Y,YYY - year with comma (full PgSQL datetime range) digits)
YYYY - year (4 and more (full PgSQL datetime range) digits)
YYY - last 3 digits of year
YY - last 2 digits of year
Y - last digit of year
MONTH - full month name (upper) (9-letter)
Month - full month name - first character is upper (9-letter)
month - full month name - all characters is upper (9-letter)
MON - abbreviated month name (3-letter)
Mon - abbreviated month name (3-letter) - first character is upper
mon - abbreviated month name (3-letter) - all characters is upper
MM - month (01-12)
DAY - full day name (upper) (9-letter)
Day - full day name - first character is upper (9-letter)
day - full day name - all characters is upper (9-letter)
DY - abbreviated day name (3-letter) (upper)
Dy - abbreviated day name (3-letter) - first character is upper
Dy - abbreviated day name (3-letter) - all character is upper
DDD - day of year (001-366)
DD - day of month (01-31)
D - day of week (1-7; SUN=1)
WW - week number of year
CC - century (2-digits)
Q - quarter
RM - roman numeral month (I=JAN; I-XII)
W - week of month
J - julian day (days since January 1, 4712 BC)

* Other:
\
- must be use as double \\
- if \\ is in front " is \\ direct character
ex:
\\ -is-> \
\\" -is-> "

" of text "
- all between " is output as text (not parsed)

* Note:

- as base for date and time is used full PostgreSQL DateTime range

* Examples:

template1=> select to_char('now', 'HH24:MI:SS, Day, Month, Y,YYY');
to_char
-------------------------------------
16:53:17, Friday , October , 1,999

template1=> select to_char('now', 'HH24:MI:SS, FMDay, FMMonth, Y,YYY');
to_char
--------------------------------
16:55:47, Friday, October, 1,999

template1=> select to_char('now', 'DDDth DDDTH SSth Y,YYYth FMSSth');
to_char
----------------------------
288th 288TH 02nd 1,999th 2nd

template1=> select to_char('now', 'Hello HH:MI:SS day');
to_char
------------------------
Hello 05:00:12 friday

template1=> select to_char('now', 'Hello "day" HH:MI:SS day');
to_char
----------------------------
Hello day 05:00:33 friday

template1=> select to_char('now', '\\"Hello "day" HH:MI:SS FMday\\"');
to_char
---------------------------
"Hello day 05:01:15 friday"

template1=> select to_char('now', 'HH\\MI\\SS');
to_char
--------
05\10\29

---end-to_char()

------------------------------------------------------------------------------
ORDINAL(int4, text)

* Translate number to ordinal number and return this as text

* Examples:

template1=> select ordinal(21212, 'TH');
ordinal
-------
21212ND

template1=> select ordinal(21212, 'th');
ordinal
-------
21212nd

---end-ordinal()

------------------------------------------------------------------------------