pg>mysql>mathematical.sql
2021-09-24 15:55:51    18    0    0
ivan

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

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

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

 

1 -- ATAN2()

-- Two parameter alias of atan2

CREATE OR REPLACE FUNCTION atan(double precision, double precision)

RETURNS double precision AS $$

  SELECT pg_catalog.atan2($1, $2)

$$ IMMUTABLE STRICT LANGUAGE SQL;

 

2 -- CRC32()

CREATE OR REPLACE FUNCTION crc32(text)

RETURNS bigint AS $$

  DECLARE

    -- crc look up table

    -- based on the polynomial:

    -- x^32+x^26+x^23+x^22+x^16+x^12+x^11+x^10+x^8+x^7+x^5+x^4+x^2+x+1

 

    crc_lu bigint[256] := ARRAY[0, 1996959894, 3993919788, 2567524794,

      124634137, 1886057615, 3915621685, 2657392035, 249268274, 2044508324,

      3772115230, 2547177864, 162941995, 2125561021, 3887607047, 2428444049,

      498536548, 1789927666, 4089016648, 2227061214, 450548861, 1843258603,

      4107580753, 2211677639, 325883990, 1684777152, 4251122042,

      2321926636, 335633487, 1661365465, 4195302755, 2366115317,

      997073096, 1281953886, 3579855332, 2724688242, 1006888145,

      1258607687, 3524101629, 2768942443, 901097722, 1119000684,

      3686517206, 2898065728, 853044451, 1172266101, 3705015759,

      2882616665, 651767980, 1373503546, 3369554304, 3218104598,

      565507253, 1454621731, 3485111705, 3099436303, 671266974,

      1594198024, 3322730930, 2970347812, 795835527, 1483230225,

      3244367275, 3060149565, 1994146192, 31158534, 2563907772,

      4023717930, 1907459465, 112637215, 2680153253, 3904427059,

      2013776290, 251722036, 2517215374, 3775830040, 2137656763,

      141376813, 2439277719, 3865271297, 1802195444, 476864866,

      2238001368, 4066508878, 1812370925, 453092731, 2181625025,

      4111451223, 1706088902, 314042704, 2344532202, 4240017532,

      1658658271, 366619977, 2362670323, 4224994405, 1303535960,

      984961486, 2747007092, 3569037538, 1256170817, 1037604311,

      2765210733, 3554079995, 1131014506, 879679996, 2909243462,

      3663771856, 1141124467, 855842277, 2852801631, 3708648649,

      1342533948, 654459306, 3188396048, 3373015174, 1466479909,

      544179635, 3110523913, 3462522015, 1591671054, 702138776,

      2966460450, 3352799412, 1504918807, 783551873, 3082640443,

      3233442989, 3988292384, 2596254646, 62317068, 1957810842,

      3939845945, 2647816111, 81470997, 1943803523, 3814918930,

      2489596804, 225274430, 2053790376, 3826175755, 2466906013,

      167816743, 2097651377, 4027552580, 2265490386, 503444072,

      1762050814, 4150417245, 2154129355, 426522225, 1852507879,

      4275313526, 2312317920, 282753626, 1742555852, 4189708143,

      2394877945, 397917763, 1622183637, 3604390888, 2714866558,

      953729732, 1340076626, 3518719985, 2797360999, 1068828381,

      1219638859, 3624741850, 2936675148, 906185462, 1090812512,

      3747672003, 2825379669, 829329135, 1181335161, 3412177804,

      3160834842, 628085408, 1382605366, 3423369109, 3138078467,

      570562233, 1426400815, 3317316542, 2998733608, 733239954,

      1555261956, 3268935591, 3050360625, 752459403, 1541320221,

      2607071920, 3965973030, 1969922972, 40735498, 2617837225,

      3943577151, 1913087877, 83908371, 2512341634, 3803740692,

      2075208622, 213261112, 2463272603, 3855990285, 2094854071,

      198958881, 2262029012, 4057260610, 1759359992, 534414190,

      2176718541, 4139329115, 1873836001, 414664567, 2282248934,

      4279200368, 1711684554, 285281116, 2405801727, 4167216745,

      1634467795, 376229701, 2685067896, 3608007406, 1308918612,

      956543938, 2808555105, 3495958263, 1231636301, 1047427035,

      2932959818, 3654703836, 1088359270, 936918000, 2847714899,

      3736837829, 1202900863, 817233897, 3183342108, 3401237130,

      1404277552, 615818150, 3134207493, 3453421203, 1423857449,

      601450431, 3009837614, 3294710456, 1567103746, 711928724,

      3020668471, 3272380065, 1510334235, 755167117];

    crc bigint := 4294967295; -- must use bigint, because we don't have

                              -- an unsigned int32

    len int;

    data ALIAS FOR $1;

    tmp bigint;

    pos int := 1;

    c text;

    d bigint;

  BEGIN

    len = pg_catalog.length(data);

    WHILE len >= pos LOOP

        c := SUBSTRING(data FROM pos FOR 1);

        d := pg_catalog.ascii(c);

        pos = pos + 1;

        tmp = (crc # d) & 255;

        crc = crc_lu[tmp + 1] # (crc >> 8);

    END LOOP;

    crc := crc # 4294967295;

    RETURN crc;

  END

$$ IMMUTABLE STRICT LANGUAGE plpgsql;

 

3 -- FORMAT()

-- FIXME: Only handles numbers with integer part up to 21 digits

--

-- MySQL has some bugs with long numbers, but they're not worth replicating:

-- format(9999999999999999999999999999999999999999999999999999999999999999, 10)

--  --> last group has four nines!

-- format(9999999999999999999999999999999999999999999999999999999999999999999999999, 1);

--  --> last group has five nines and no decmial zero

CREATE OR REPLACE FUNCTION format(numeric, integer)

RETURNS text AS $$

  SELECT pg_catalog.to_char(pg_catalog.round($1, $2), 'FM999,999,999,999,999,999,990'

    operator(pg_catalog.||)

      case when $2 > 0 then

        '.' operator(pg_catalog.||) pg_catalog.repeat('0', $2)

      else '' end)

$$ IMMUTABLE STRICT LANGUAGE SQL;

 

4 -- LN(), LOG()

-- Not reimplemented but note that PostgreSQL has an error on -ve values,

-- but MySQL just returns NULL.

 

5 -- LOG2()

CREATE OR REPLACE FUNCTION log2(numeric)

RETURNS numeric AS $$

  SELECT CASE WHEN $1 > 0 THEN pg_catalog.log(2, $1) ELSE NULL END

$$ IMMUTABLE STRICT LANGUAGE SQL;

 

6 -- LOG10()

CREATE OR REPLACE FUNCTION log10(numeric)

RETURNS numeric AS $$

  SELECT CASE WHEN $1 > 0 THEN pg_catalog.log(10, $1) ELSE NULL END

$$ IMMUTABLE STRICT LANGUAGE SQL;

 

7 -- MOD()

-- PostgreSQL has all MOD usage the same as MySQL EXCEPT this will not work:

--

-- SELECT 29 MOD 9;

 

8 -- RAND()

CREATE OR REPLACE FUNCTION rand() RETURNS double precision AS $$

  SELECT pg_catalog.random()

$$ VOLATILE LANGUAGE SQL;

CREATE OR REPLACE FUNCTION rand(integer) RETURNS double precision AS $$

  SELECT pg_catalog.setseed($1);

  SELECT pg_catalog.random()

$$ VOLATILE LANGUAGE SQL;

 

9 -- SQRT()

-- Not reimplemented but note that PostgreSQL has an error on -ve values,

-- but MySQL just returns NULL.

 

10 -- TRUNCATE()

CREATE OR REPLACE FUNCTION truncate(numeric, integer)

RETURNS numeric AS $$

  SELECT pg_catalog.trunc($1, $2)

$$ IMMUTABLE STRICT LANGUAGE SQL;


Pre: pg>mysql>information.sql

Next: pg>mysql>operators.sql

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