目录
环境
文档用途
详细信息
环境
系统平台：Linux x86-64 Red Hat Enterprise Linux 7
版本：6.0,5.6.5,4.5,4.3.4.9
文档用途
从MySQL数据库迁移到瀚高数据库，瀚高数据库中没有date_format函数，为满足客户需求需要自定义同名兼容函数。
详细信息
--date_format函数主体：
CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
DECLARE
i int := 1;
temp text := '';
c text;
n text;
res text;
BEGIN
WHILE i <= pg_catalog.length($2) LOOP
c := SUBSTRING ($2 FROM i FOR 1);
IF c = '%' AND i != pg_catalog.length($2) THEN
n := SUBSTRING ($2 FROM (i + 1) FOR 1);
SELECT INTO res CASE
WHEN n = 'a' THEN pg_catalog.to_char($1, 'Dy')
WHEN n = 'b' THEN pg_catalog.to_char($1, 'Mon')
WHEN n = 'c' THEN pg_catalog.to_char($1, 'FMMM')
WHEN n = 'D' THEN pg_catalog.to_char($1, 'FMDDth')
WHEN n = 'd' THEN pg_catalog.to_char($1, 'DD')
WHEN n = 'e' THEN pg_catalog.to_char($1, 'FMDD')
WHEN n = 'f' THEN pg_catalog.to_char($1, 'US')
WHEN n = 'H' THEN pg_catalog.to_char($1, 'HH24')
WHEN n = 'h' THEN pg_catalog.to_char($1, 'HH12')
WHEN n = 'I' THEN pg_catalog.to_char($1, 'HH12')
WHEN n = 'i' THEN pg_catalog.to_char($1, 'MI')
WHEN n = 'j' THEN pg_catalog.to_char($1, 'DDD')
WHEN n = 'k' THEN pg_catalog.to_char($1, 'FMHH24')
WHEN n = 'l' THEN pg_catalog.to_char($1, 'FMHH12')
WHEN n = 'M' THEN pg_catalog.to_char($1, 'FMMonth')
WHEN n = 'm' THEN pg_catalog.to_char($1, 'MM')
WHEN n = 'p' THEN pg_catalog.to_char($1, 'AM')
WHEN n = 'r' THEN pg_catalog.to_char($1, 'HH12:MI:SS AM')
WHEN n = 'S' THEN pg_catalog.to_char($1, 'SS')
WHEN n = 's' THEN pg_catalog.to_char($1, 'SS')
WHEN n = 'T' THEN pg_catalog.to_char($1, 'HH24:MI:SS')
WHEN n = 'U' THEN pg_catalog.lpad(week($1::date, 0)::text, 2, '0')
WHEN n = 'u' THEN pg_catalog.lpad(week($1::date, 1)::text, 2, '0')
WHEN n = 'V' THEN pg_catalog.lpad(week($1::date, 2)::text, 2, '0')
WHEN n = 'v' THEN pg_catalog.lpad(week($1::date, 3)::text, 2, '0')
WHEN n = 'W' THEN pg_catalog.to_char($1, 'FMDay')
WHEN n = 'w' THEN EXTRACT(DOW FROM $1)::text
WHEN n = 'X' THEN pg_catalog.lpad(((_calc_week($1::date, _week_mode(2)))[2])::text, 4, '0')
WHEN n = 'x' THEN pg_catalog.lpad(((_calc_week($1::date, _week_mode(3)))[2])::text, 4, '0')
WHEN n = 'Y' THEN pg_catalog.to_char($1, 'YYYY')
WHEN n = 'y' THEN pg_catalog.to_char($1, 'YY')
WHEN n = '%' THEN pg_catalog.to_char($1, '%')
ELSE NULL
END;
temp := temp operator(pg_catalog.||) res;
i := i + 2;
ELSE
temp = temp operator(pg_catalog.||) c;
i := i + 1;
END IF;
END LOOP;
RETURN temp;
END
$function$;
--主体函数调用的其它函数
CREATE OR REPLACE FUNCTION _week_mode(mode integer)
RETURNS integer AS $$
DECLARE
_WEEK_MONDAY_FIRST CONSTANT integer := 1;
_WEEK_FIRST_WEEKDAY CONSTANT integer := 4;
week_format integer := mode & 7;
BEGIN
IF (week_format & _WEEK_MONDAY_FIRST) = 0 THEN
week_format := week_format # _WEEK_FIRST_WEEKDAY;
END IF;
RETURN week_format;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION _calc_weekday(qdate date, sundayfirst boolean)
RETURNS integer AS $$
BEGIN
RETURN (EXTRACT(DOW FROM qdate)::integer + CASE WHEN sundayfirst THEN 0 ELSE 6 END) % 7;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION _calc_days_in_year(year integer)
RETURNS integer AS $$
BEGIN
IF (year & 3) = 0 AND ((year % 100) <> 0 OR (year % 400) = 0 AND year <> 0) THEN
RETURN 366;
ELSE
RETURN 365;
END IF;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION _calc_week(qdate anyelement, behavior integer)
RETURNS integer[] AS $$
DECLARE
_WEEK_MONDAY_FIRST CONSTANT integer := 1;
_WEEK_YEAR CONSTANT integer := 2;
_WEEK_FIRST_WEEKDAY CONSTANT integer := 4;
qyear integer := EXTRACT(YEAR FROM qdate);
qmonth integer := EXTRACT(MONTH FROM qdate);
qday integer := EXTRACT(DAY FROM qdate);
daynr integer := EXTRACT(DOY FROM qdate);
yday1 date := pg_catalog.date_trunc('year', qdate);
first_daynr integer := 1;
monday_first boolean := (behavior & _WEEK_MONDAY_FIRST) <> 0;
week_year boolean := (behavior & _WEEK_YEAR) <> 0;
first_weekday boolean := (behavior & _WEEK_FIRST_WEEKDAY) <> 0;
weekday integer := _calc_weekday(yday1, NOT monday_first);
days integer;
BEGIN
IF qmonth = 1 AND qday <= 7 - weekday THEN
IF (NOT week_year) AND ((first_weekday AND weekday <> 0) OR (NOT first_weekday AND weekday >= 4)) THEN
RETURN array[0, qyear];
END IF;
week_year := true;
qyear := qyear - 1;
days := _calc_days_in_year(qyear);
first_daynr := first_daynr - days;
weekday := (weekday + 53 * 7 - days) % 7;
END IF;
IF (first_weekday AND weekday <> 0) OR (NOT first_weekday AND weekday >= 4) THEN
days := daynr - (first_daynr + (7 - weekday));
ELSE
days := daynr - (first_daynr - weekday);
END IF;
IF week_year AND days >= 52 * 7 THEN
weekday := (weekday + _calc_days_in_year(qyear)) % 7;
IF (NOT first_weekday AND weekday < 4) OR (first_weekday AND weekday = 0) THEN
qyear := qyear + 1;
RETURN array[1, qyear];
END IF;
END IF;
RETURN array[days / 7 + 1, qyear];
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION week(anyelement, integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN (_calc_week($1, _week_mode($2)))[1];
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$function$;
--调用函数：
select DATE_FORMAT(now()::timestamp ,'%Y-%m-%d');
select DATE_FORMAT(now()::timestamp,'%Y-%m-%d %H:%i');
select DATE_FORMAT(now()::timestamp,'%Y');
select DATE_FORMAT(now()::timestamp,'%Y-%m');
select DATE_FORMAT(now()::timestamp,'%m-%d');
select DATE_FORMAT(now()::timestamp,'%m-%d %H:%i');
select DATE_FORMAT(now()::timestamp,'%Y年%m月%d日');
select DATE_FORMAT(now()::timestamp,'%Y-%m-%d %H:%i:%s');
