pg>mysql>casts.sql
2021-09-24 16:23:20    26    0    0
ivan

mysqlcompat-0.0.7是网上开源的postgresql兼容包;

以下是写sql兼容的方式,兼容mysql函数,此种方式效率会是瓶颈;

因此尽量改成postgresql原生的函数或者底层源码兼容;

 

1 -- TIME TO INTEGER

CREATE OR REPLACE FUNCTION _time_to_integer(time with time zone)

RETURNS integer AS $$

  SELECT

    EXTRACT(HOUR FROM $1)::integer * 10000

    + EXTRACT(MINUTE FROM $1)::integer * 100

    + EXTRACT(SECONDS FROM $1)::integer

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (time with time zone AS integer)

  WITH FUNCTION _time_to_integer (time with time zone)

  AS IMPLICIT;

CREATE OR REPLACE FUNCTION _time_to_integer(time without time zone)

RETURNS integer AS $$

  SELECT

    EXTRACT(HOUR FROM $1)::integer * 10000

    + EXTRACT(MINUTE FROM $1)::integer * 100

    + EXTRACT(SECONDS FROM $1)::integer

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (time without time zone AS integer)

  WITH FUNCTION _time_to_integer (time without time zone)

  AS IMPLICIT;

 

2 -- DATE TO INTEGER

-- Note: requires explicit casts in some cases

CREATE OR REPLACE FUNCTION _date_to_integer(date)

RETURNS integer AS $$

  SELECT

    EXTRACT(YEAR FROM $1)::integer * 10000

    + EXTRACT(MONTH FROM $1)::integer * 100

    + EXTRACT(DAY FROM $1)::integer

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (date AS integer)

  WITH FUNCTION _date_to_integer (date)

  AS IMPLICIT;

 

3 -- TIMESTAMP TO BIGINT

-- Depends on: _time_to_integer and _date_to_integer

CREATE OR REPLACE FUNCTION _timestamp_to_bigint(timestamp with time zone)

RETURNS bigint AS $$

  SELECT _date_to_integer($1::date)::bigint * 1000000 + _time_to_integer($1::time)

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (timestamp with time zone AS bigint)

  WITH FUNCTION _timestamp_to_bigint (timestamp with time zone)

  AS IMPLICIT;

CREATE OR REPLACE FUNCTION _timestamp_to_bigint(timestamp without time zone)

RETURNS bigint AS $$

  SELECT _date_to_integer($1::date)::bigint * 1000000 + _time_to_integer($1::time)

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (timestamp without time zone AS bigint)

  WITH FUNCTION _timestamp_to_bigint (timestamp without time zone)

  AS IMPLICIT;

 

4 -- INTERVAL TO BIGINT

CREATE OR REPLACE FUNCTION _interval_to_bigint(interval)

RETURNS bigint AS $$

  SELECT

    EXTRACT(YEAR FROM $1)::bigint * 10000000000

    + EXTRACT(MONTH FROM $1)::bigint * 100000000

    + EXTRACT(DAY FROM $1)::bigint * 1000000

    + EXTRACT(HOUR FROM $1)::bigint * 10000

    + EXTRACT(MINUTE FROM $1)::bigint * 100

    + EXTRACT(SECONDS FROM $1)::bigint

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE CAST (interval AS bigint)

  WITH FUNCTION _interval_to_bigint (interval)

  AS IMPLICIT;

Pre: pg>mysql>bit.sql

Next: pg>mysql>controlflow.sql

26
Sign in to leave a comment.
No Leanote account? Sign up now.
0 comments
Table of content