ivan
mysqlcompat-0.0.7是网上开源的postgresql兼容包；
以下是写sql兼容的方式，兼容mysql函数，此种方式效率会是瓶颈；
因此尽量改成postgresql原生的函数或者底层源码兼容；
1 -- ADDDATE()
-- Note: passing in the interval is different
CREATE OR REPLACE FUNCTION adddate(timestamp without time zone, interval)
RETURNS timestamp without time zone AS $$
SELECT $1 + $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE OR REPLACE FUNCTION adddate(timestamp without time zone, integer)
RETURNS timestamp without time zone AS $$
SELECT $1 + (INTERVAL '1 day' * $2)
$$ IMMUTABLE STRICT LANGUAGE SQL;
2 -- ADDTIME()
-- Note: requires casting if you install both versions
CREATE OR REPLACE FUNCTION addtime(timestamp without time zone, interval)
RETURNS timestamp without time zone AS $$
SELECT $1 + $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE OR REPLACE FUNCTION addtime(interval, interval)
RETURNS interval AS $$
SELECT $1 + $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
3 -- CONVERT_TZ()
CREATE OR REPLACE FUNCTION convert_tz(timestamp without time zone, text, text)
RETURNS timestamp without time zone AS $$
SELECT CASE
WHEN POSITION(':' IN $3) = 0 THEN
($1 operator(pg_catalog.||) ' ' operator(pg_catalog.||) $2)::timestamp with time zone AT TIME ZONE $3
ELSE
($1 operator(pg_catalog.||) ' ' operator(pg_catalog.||) $2)::timestamp with time zone AT TIME ZONE $3::interval
END
$$ IMMUTABLE STRICT LANGUAGE SQL;
4 -- CURDATE()
CREATE OR REPLACE FUNCTION curdate()
RETURNS date AS $$
SELECT CURRENT_DATE
$$ VOLATILE LANGUAGE SQL;
5 -- CURTIME()
CREATE OR REPLACE FUNCTION curtime()
RETURNS time without time zone AS $$
SELECT LOCALTIME(0)
$$ VOLATILE LANGUAGE SQL;
6 -- DATEDIFF()
CREATE OR REPLACE FUNCTION datediff(date, date)
RETURNS integer AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
7 -- DATE_ADD()
CREATE OR REPLACE FUNCTION date_add(timestamp without time zone, interval)
RETURNS timestamp without time zone AS $$
SELECT $1 + $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
8 -- DATE_FORMAT()
CREATE OR REPLACE FUNCTION date_format(timestamp without time zone, text)
RETURNS text AS $$
DECLARE
i int := 1;
temp text := '';
c text;
n text;
res text;
BEGIN
WHILE i <= pg_catalog.length($2) LOOP
-- Look at current character
c := SUBSTRING ($2 FROM i FOR 1);
-- If it's a '%' and not the last character then process it as a placeholder
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
-- Otherwise just append the character to the string
temp = temp operator(pg_catalog.||) c;
i := i + 1;
END IF;
END LOOP;
RETURN temp;
END
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
9 -- DATE_SUB()
CREATE OR REPLACE FUNCTION date_sub(timestamp without time zone, interval)
RETURNS timestamp without time zone AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
10 -- DAY()
CREATE OR REPLACE FUNCTION day(date)
RETURNS integer AS $$
SELECT EXTRACT(DAY FROM DATE($1))::integer
$$ IMMUTABLE STRICT LANGUAGE SQL;
11 -- DAYNAME()
CREATE OR REPLACE FUNCTION dayname(date)
RETURNS text AS $$
SELECT pg_catalog.to_char($1, 'FMDay')
$$ IMMUTABLE STRICT LANGUAGE SQL;
12 -- FROM_DAYS()
CREATE OR REPLACE FUNCTION from_days(integer)
RETURNS date AS $$
SELECT ('0001-01-01 BC'::date + $1 * INTERVAL '1 day')::date
$$ IMMUTABLE STRICT LANGUAGE SQL;
13 -- FROM_UNIXTIME()
-- Returns local time? Is this actually the same as MySQL?
-- Depends on: DATE_FORMAT()
CREATE OR REPLACE FUNCTION from_unixtime(bigint)
RETURNS timestamp without time zone AS $$
SELECT pg_catalog.to_timestamp($1)::timestamp without time zone
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE OR REPLACE FUNCTION from_unixtime(bigint, text)
RETURNS text AS $$
SELECT date_format(from_unixtime($1), $2)
$$ IMMUTABLE STRICT LANGUAGE SQL;
14 -- GET_FORMAT()
-- Note that first parameter needs to be quoted in this version
CREATE OR REPLACE FUNCTION get_format(text, text)
RETURNS text AS $$
SELECT CASE
WHEN $1 ILIKE 'DATE' THEN
CASE WHEN $2 ILIKE 'USA' THEN '%m.%d.%Y'
WHEN $2 ILIKE 'JIS' OR $2 ILIKE 'ISO' THEN '%Y-%m-%d'
WHEN $2 ILIKE 'EUR' THEN '%d.%m.%Y'
WHEN $2 ILIKE 'INTERNAL' THEN '%Y%m%d'
ELSE NULL
END
WHEN $1 ILIKE 'DATETIME' THEN
CASE WHEN $2 ILIKE 'USA' OR $2 ILIKE 'EUR' THEN '%Y-%m-%d-%H.%i.%s'
WHEN $2 ILIKE 'JIS' OR $2 ILIKE 'ISO' THEN '%Y-%m-%d %H:%i:%s'
WHEN $2 ILIKE 'INTERNAL' THEN '%Y%m%d%H%i%s'
ELSE NULL
END
WHEN $1 ILIKE 'TIME' THEN
CASE WHEN $2 ILIKE 'USA' THEN '%h:%i:%s %p'
WHEN $2 ILIKE 'JIS' OR $2 ILIKE 'ISO' THEN '%H:%i:%s'
WHEN $2 ILIKE 'EUR' THEN 'H.%i.%S'
WHEN $2 ILIKE 'INTERNAL' THEN '%H%i%s'
ELSE NULL
END
ELSE
NULL
END
$$ IMMUTABLE STRICT LANGUAGE SQL;
15 -- LAST_DAY()
-- Note that for illegal timestamps this function raises an error,
-- whereas under MySQL it returns NULL
CREATE OR REPLACE FUNCTION last_day(timestamp)
RETURNS date AS $$
SELECT CASE
WHEN EXTRACT(MONTH FROM $1) = 12 THEN
(((EXTRACT(YEAR FROM $1) + 1) operator(pg_catalog.||) '-01-01')::date - INTERVAL '1 day')::date
ELSE
((EXTRACT(YEAR FROM $1) operator(pg_catalog.||) '-' operator(pg_catalog.||) (EXTRACT(MONTH FROM $1) + 1) operator(pg_catalog.||) '-01')::date - INTERVAL '1 day')::date
END
$$ IMMUTABLE STRICT LANGUAGE SQL;
16 -- MAKEDATE()
CREATE OR REPLACE FUNCTION makedate(integer, integer)
RETURNS date AS $$
SELECT CASE WHEN $2 > 0 THEN
(($1 operator(pg_catalog.||) '-01-01')::date + ($2 - 1) * INTERVAL '1 day')::date
ELSE
NULL
END
$$ IMMUTABLE STRICT LANGUAGE SQL;
17 -- MAKETIME()
CREATE OR REPLACE FUNCTION maketime(integer, integer, integer)
RETURNS interval AS $$
SELECT ($1 operator(pg_catalog.||) ':' operator(pg_catalog.||) $2 opera-tor(pg_catalog.||) ':' operator(pg_catalog.||) $3)::interval
$$ IMMUTABLE STRICT LANGUAGE SQL;
18 -- MONTHNAME()
CREATE OR REPLACE FUNCTION monthname(date)
RETURNS text AS $$
SELECT pg_catalog.to_char($1, 'FMMonth')
$$ IMMUTABLE STRICT LANGUAGE SQL;
19 -- PERIOD_ADD()
CREATE OR REPLACE FUNCTION period_add(integer, integer)
RETURNS text AS $$
DECLARE
period text;
base date;
baseyear integer;
BEGIN
IF pg_catalog.length($1) < 4 THEN
period := pg_catalog.lpad($1, 4, 0);
ELSIF pg_catalog.length($1) = 5 THEN
period := pg_catalog.lpad($1, 6, 0);
ELSE
period := $1;
END IF;
IF pg_catalog.length(period) = 4 THEN
baseyear := SUBSTRING(period FROM 1 FOR 2);
IF baseyear BETWEEN 70 AND 99 THEN
baseyear := baseyear + 1900;
ELSE
baseyear := baseyear + 2000;
END IF;
base := (baseyear operator(pg_catalog.||) '-' operator(pg_catalog.||) SUB-STRING(period FROM 3) operator(pg_catalog.||) '-01')::date;
ELSIF pg_catalog.length(period) = 6 THEN
base := (SUBSTRING(period FROM 1 FOR 4) operator(pg_catalog.||) '-' opera-tor(pg_catalog.||) SUBSTRING(period FROM 5) operator(pg_catalog.||) '-01')::date;
ELSE
RETURN NULL;
END IF;
base := base + (INTERVAL '1 month' * $2);
RETURN pg_catalog.lpad(EXTRACT(YEAR FROM base), 4, '0') operator(pg_catalog.||) pg_catalog.lpad(EXTRACT(MONTH FROM base), 2, '0');
END
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
20 -- PERIOD_DIFF()
CREATE OR REPLACE FUNCTION period_diff(integer, integer)
RETURNS integer AS $$
DECLARE
baseyear integer;
period1 text;
period2 text;
months1 integer;
months2 integer;
BEGIN
IF pg_catalog.length($1) < 4 THEN
period1 := pg_catalog.lpad($1, 4, 0);
ELSIF pg_catalog.length($1) = 5 THEN
period1 := pg_catalog.lpad($1, 6, 0);
ELSE
period1 := $1;
END IF;
IF pg_catalog.length(period1) = 4 THEN
baseyear := SUBSTRING(period1 FROM 1 FOR 2);
IF baseyear BETWEEN 70 AND 99 THEN
baseyear := baseyear + 1900;
ELSE
baseyear := baseyear + 2000;
END IF;
months1 := baseyear * 12 + SUBSTRING(period1 FROM 3)::integer;
ELSIF pg_catalog.length(period1) = 6 THEN
months1 := SUBSTRING(period1 FROM 1 FOR 4)::integer * 12 + SUBSTRING(period1 FROM 5)::integer;
ELSE
RETURN NULL;
END IF;
IF pg_catalog.length($2) < 4 THEN
period2 := pg_catalog.lpad($2, 4, 0);
ELSIF pg_catalog.length($2) = 5 THEN
period2 := pg_catalog.lpad($2, 6, 0);
ELSE
period2 := $2;
END IF;
IF pg_catalog.length(period2) = 4 THEN
baseyear := SUBSTRING(period2 FROM 1 FOR 2);
IF baseyear BETWEEN 70 AND 99 THEN
baseyear := baseyear + 1900;
ELSE
baseyear := baseyear + 2000;
END IF;
months2 := baseyear * 12 + SUBSTRING(period2 FROM 3)::integer;
ELSIF pg_catalog.length(period2) = 6 THEN
months2 := SUBSTRING(period2 FROM 1 FOR 4)::integer * 12 + SUBSTRING(period2 FROM 5)::integer;
ELSE
RETURN NULL;
END IF;
RETURN months1 - months2;
END
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
21 -- SEC_TO_TIME()
CREATE OR REPLACE FUNCTION sec_to_time(bigint)
RETURNS interval AS $$
SELECT $1 * INTERVAL '1 second'
$$ IMMUTABLE STRICT LANGUAGE SQL;
22 -- STR_TO_DATE()
-- Note: Doesn't handle weeks of years yet and will return different results
-- to MySQL if you pass in an invalid timestamp
CREATE OR REPLACE FUNCTION str_to_date(text, text)
RETURNS timestamp without time zone AS $$
DECLARE
i int := 1;
temp text := '';
c text;
n text; res text;
BEGIN
WHILE i <= pg_catalog.length($2) LOOP
-- Look at current character
c := SUBSTRING ($2 FROM i FOR 1);
-- If it's a '%' and not the last character then process it as a placeholder
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 'Dy'
WHEN n = 'b' THEN 'Mon'
WHEN n = 'c' THEN 'FMMM'
WHEN n = 'D' THEN 'FMDDth'
WHEN n = 'd' THEN 'DD'
WHEN n = 'e' THEN 'FMDD'
WHEN n = 'f' THEN 'US'
WHEN n = 'H' THEN 'HH24'
WHEN n = 'h' THEN 'HH12'
WHEN n = 'I' THEN 'HH12'
WHEN n = 'i' THEN 'MI'
WHEN n = 'j' THEN 'DDD'
WHEN n = 'k' THEN 'FMHH24'
WHEN n = 'l' THEN 'FMHH12'
WHEN n = 'M' THEN 'FMMonth'
WHEN n = 'm' THEN 'MM'
WHEN n = 'p' THEN 'AM'
WHEN n = 'r' THEN 'HH12:MI:SS AM'
WHEN n = 'S' THEN 'SS'
WHEN n = 's' THEN 'SS'
WHEN n = 'T' THEN 'HH24:MI:SS'
WHEN n = 'U' THEN '?'
WHEN n = 'u' THEN '?'
WHEN n = 'V' THEN '?'
WHEN n = 'v' THEN '?'
WHEN n = 'W' THEN 'FMDay'
WHEN n = 'w' THEN '?'
WHEN n = 'X' THEN '?'
WHEN n = 'x' THEN '?'
WHEN n = 'Y' THEN 'YYYY'
WHEN n = 'y' THEN 'YY'
WHEN n = '%' THEN '%'
ELSE NULL
END;
temp := temp operator(pg_catalog.||) res;
i := i + 2;
ELSE
-- Otherwise just append the character to the string
temp = temp operator(pg_catalog.||) c;
i := i + 1;
END IF;
END LOOP;
RETURN pg_catalog.to_timestamp($1, temp)::timestamp without time zone;
END
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
23 -- SUBDATE()
-- Note: passing in the interval is different
CREATE OR REPLACE FUNCTION subdate(timestamp without time zone, interval)
RETURNS timestamp without time zone AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE OR REPLACE FUNCTION subdate(timestamp without time zone, integer)
RETURNS timestamp without time zone AS $$
SELECT $1 - (INTERVAL '1 day' * $2)
$$ IMMUTABLE STRICT LANGUAGE SQL;
24 -- SUBTIME
-- Note: requires casting if you install both versions
CREATE OR REPLACE FUNCTION subtime(timestamp without time zone, interval)
RETURNS timestamp without time zone AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE OR REPLACE FUNCTION subtime(interval, interval)
RETURNS interval AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
25 -- SYSDATE()
CREATE OR REPLACE FUNCTION sysdate()
RETURNS timestamp without time zone AS $$
SELECT pg_catalog.timeofday()::timestamp(0) without time zone
$$ VOLATILE LANGUAGE SQL;
26 -- TIME()
-- Not possible to implement
27 -- TIMEDIFF()
-- Note: requires casting if you install both versions
CREATE OR REPLACE FUNCTION timediff(timestamp without time zone, timestamp without time zone)
RETURNS interval AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
CREATE OR REPLACE FUNCTION timediff(time without time zone, time without time zone)
RETURNS interval AS $$
SELECT $1 - $2
$$ IMMUTABLE STRICT LANGUAGE SQL;
28 -- TIMESTAMP()
-- Not possible to implement
29 -- TIMESTAMPADD()
-- Note that first parameter needs to be quoted in this version
CREATE OR REPLACE FUNCTION timestampadd(text, integer, timestamp without time zone)
RETURNS timestamp without time zone AS $$
SELECT $3 + ($2 operator(pg_catalog.||) ' ' operator(pg_catalog.||) $1)::interval
$$ IMMUTABLE STRICT LANGUAGE SQL;
30 -- TIMESTAMPDIFF()
-- Note that first parameter needs to be quoted in this version
31 -- TIME_FORMAT()
CREATE OR REPLACE FUNCTION time_format(interval, text)
RETURNS text AS $$
DECLARE
i int := 1;
temp text := '';
c text;
n text;
res text;
BEGIN
WHILE i <= pg_catalog.length($2) LOOP
-- Look at current character
c := SUBSTRING ($2 FROM i FOR 1);
-- If it's a '%' and not the last character then process it as a placeholder
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 '0'
WHEN n = 'b' THEN '0'
WHEN n = 'c' THEN '0'
WHEN n = 'D' THEN '0'
WHEN n = 'd' THEN '0'
WHEN n = 'e' THEN '0'
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.lpad(pg_catalog.to_char($1, 'HH12')::integer % 12, 2, '0')
WHEN n = 'I' THEN pg_catalog.lpad(pg_catalog.to_char($1, 'HH12')::integer % 12, 2, '0')
WHEN n = 'i' THEN pg_catalog.to_char($1, 'MI')
WHEN n = 'j' THEN '0'
WHEN n = 'k' THEN pg_catalog.to_char($1, 'FMHH24')
WHEN n = 'l' THEN (pg_catalog.to_char($1, 'FMHH12')::integer % 12)::text
WHEN n = 'M' THEN '0'
WHEN n = 'm' THEN '0'
WHEN n = 'p' THEN pg_catalog.to_char($1, 'AM')
WHEN n = 'r' THEN pg_catalog.lpad(pg_catalog.to_char($1, 'HH12')::integer % 12, 2, '0')
operator(pg_catalog.||)
pg_catalog.to_char($1, ':MI:SS ')
operator(pg_catalog.||)
CASE WHEN pg_catalog.to_char($1, 'FMHH24')::integer <= 11 THEN 'AM' ELSE 'PM' END
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 '0'
WHEN n = 'u' THEN '0'
WHEN n = 'V' THEN '0'
WHEN n = 'v' THEN '0'
WHEN n = 'W' THEN '0'
WHEN n = 'w' THEN '0'
WHEN n = 'X' THEN '0'
WHEN n = 'x' THEN '0'
WHEN n = 'Y' THEN '0'
WHEN n = 'y' THEN '0'
WHEN n = '%' THEN pg_catalog.to_char($1, '%')
ELSE NULL
END;
temp := temp operator(pg_catalog.||) res;
i := i + 2;
ELSE
-- Otherwise just append the character to the string
temp = temp operator(pg_catalog.||) c;
i := i + 1;
END IF;
END LOOP;
RETURN temp;
END
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
32 -- TIME_TO_SEC()
CREATE OR REPLACE FUNCTION time_to_sec(interval)
RETURNS bigint AS $$
SELECT (EXTRACT(HOURS FROM $1) * 3600
+ EXTRACT(MINUTES FROM $1) * 60
+ EXTRACT(SECONDS FROM $1))::bigint
$$ IMMUTABLE STRICT LANGUAGE SQL;
33 -- TO_DAYS()
-- XXX: Haven't done integer variant
CREATE OR REPLACE FUNCTION to_days(date)
RETURNS integer AS $$
SELECT $1 - '0001-01-01 BC'::date
$$ IMMUTABLE STRICT LANGUAGE SQL;
34 -- UNIX_TIMESTAMP()
CREATE OR REPLACE FUNCTION unix_timestamp()
RETURNS bigint AS $$
SELECT EXTRACT(EPOCH FROM LOCALTIMESTAMP)::bigint
$$ VOLATILE LANGUAGE SQL;
-- XXX: This gives wrong answers? Time zones?
CREATE OR REPLACE FUNCTION unix_timestamp(timestamp without time zone)
RETURNS bigint AS $$
SELECT EXTRACT(EPOCH FROM $1)::bigint
$$ VOLATILE LANGUAGE SQL;
35 -- UTC_DATE()
CREATE OR REPLACE FUNCTION utc_date()
RETURNS date AS $$
SELECT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')::date
$$ VOLATILE LANGUAGE SQL;
36 -- UTC_TIME()
CREATE OR REPLACE FUNCTION utc_time()
RETURNS time(0) AS $$
SELECT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')::time(0)
$$ VOLATILE LANGUAGE SQL;
37 -- UTC_TIMESTAMP()
CREATE OR REPLACE FUNCTION utc_timestamp()
RETURNS timestamp(0) AS $$
SELECT (CURRENT_TIMESTAMP AT TIME ZONE 'UTC')::timestamp(0)
$$ VOLATILE LANGUAGE SQL;
38 -- WEEK()
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;
39 -- YEARWEEK()
CREATE OR REPLACE FUNCTION yearweek(qdate date, mode integer)
RETURNS integer AS $$
DECLARE
_WEEK_YEAR CONSTANT integer := 2;
a integer[] := _calc_week(qdate, _week_mode(mode | _WEEK_YEAR));
week integer := a[1];
year integer := a[2];
BEGIN
RETURN week + year * 100;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION yearweek(date)
RETURNS integer AS $$
SELECT yearweek($1, 0);
$$ IMMUTABLE STRICT LANGUAGE SQL;
--
-- is_datetime: used to determine if value passed to polymorphic
-- function is a valid date/time value
--
CREATE OR REPLACE FUNCTION is_datetime ( anyelement )
RETURNS BOOLEAN AS $$
DECLARE d date;
t TIMESTAMP;
tz TIMESTAMPTZ;
BEGIN
d = $1::DATE;
RETURN TRUE;
EXCEPTION WHEN others THEN
BEGIN
t = $1::TIMESTAMP;
RETURN TRUE;
EXCEPTION WHEN others THEN
BEGIN
tz = $1::TIMESTAMPTZ;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
END;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
--
-- Polymorphic functions to allow one function
-- to handle date / timestamp / timestamptz types
--
40 -- WEEKDAY()
CREATE OR REPLACE FUNCTION weekday( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
CASE WHEN EXTRACT(DOW FROM $1)::integer = 0 THEN
RETURN 6;
ELSE
RETURN EXTRACT(DOW FROM $1)::integer - 1;
END CASE;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
41 -- YEAR()
CREATE OR REPLACE FUNCTION year( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(YEAR FROM $1)::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
42 -- MONTH()
CREATE OR REPLACE FUNCTION month( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(MONTH FROM DATE($1))::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
43 -- DAYOFMONTH()
CREATE OR REPLACE FUNCTION dayofmonth( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(DAY FROM DATE($1))::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
44 -- DAYOFWEEK()
CREATE OR REPLACE FUNCTION dayofweek( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(DOW FROM DATE($1))::integer + 1;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
45 -- DAYOFYEAR()
CREATE OR REPLACE FUNCTION dayofyear( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(DOY FROM DATE($1))::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
46 -- HOUR()
CREATE OR REPLACE FUNCTION hour( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT (HOUR FROM $1)::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
47 -- MICROSECOND()
CREATE OR REPLACE FUNCTION microsecond( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN (EXTRACT(MICROSECONDS FROM $1))::integer % 1000000;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
48 -- MINUTE()
CREATE OR REPLACE FUNCTION minute( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(MINUTES FROM $1)::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
49 -- QUARTER()
CREATE OR REPLACE FUNCTION quarter( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(QUARTER FROM DATE($1))::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
50 -- SECOND()
CREATE OR REPLACE FUNCTION second( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN EXTRACT(SECONDS FROM $1)::integer;
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION week( anyelement, integer )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN (_calc_week($1, _week_mode($2)))[1];
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION week( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN week($1, 0);
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
51 -- WEEKOFYEAR()
CREATE OR REPLACE FUNCTION weekofyear( anyelement )
RETURNS integer AS $$
BEGIN
IF is_datetime ( $1 ) THEN
RETURN week($1, 3);
END IF;
RAISE EXCEPTION 'Invalid date / time value --> %', $1;
END;
$$ IMMUTABLE STRICT LANGUAGE PLPGSQL;
No Leanote account? Sign up now.