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
forsource code is used code from Debian md5sum. In Debian is this code distributed _without_ some restriction
underGPL. 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
tocharacter 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
characteris 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 -
fullday name - all characters is upper (9-letter) DY - abbreviated day name (3-letter) (upper) Dy -
abbreviatedday name (3-letter) - first character is upper Dy - abbreviated day name (3-letter) - all character
isupper 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()
------------------------------------------------------------------------------