PostgreSQL

2021-09-26 10:22:58    107    0    0

MySQL函数功能:

    按照关键字截取字符串,substring_index(被截取字段,关键字,关键字出现的次数)

PG建兼容函数:

create or replace function substring_index(text,text,int) 

returns text as $$

    declare

        a text;

        b text :='';

        var int;

    begin

        for var in 1..$3 loop

            a=split_part($1,$2,var);

            b=b || $2 || a;

        end loop;

    return substring(b from 2 for length(b));

    end

$$ language plpgsql

例子:

MySQL:

mysql> select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
+--------------------------------------------------------------+
| substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3) |
+--------------------------------------------------------------+
| fhafjkdalsfhiad,fjadsoj;f,ad                                 |
+--------------------------------------------------------------+
1 row in set (0.00 sec)

PG:

postgres=# select substring_index('fhafjkdalsfhiad,fjadsoj;f,adsf,dsfa','s',3);
       substring_index
------------------------------
 fhafjkdalsfhiad,fjadsoj;f,ad
(1 行记录)

 

2021-09-24 16:30:20    63    0    0

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

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

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

 

1 -- BIT_XOR

-- Note: only works for integers and bigints

CREATE OR REPLACE FUNCTION _bit_xor(bigint, bigint)

RETURNS bigint AS $$

  SELECT $1 # COALESCE($2, 0)

$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE bit_xor (

    BASETYPE = bigint,

    SFUNC = _bit_xor,

    STYPE = bigint,

    INITCOND = 0

);

 

2 -- GROUP_CONCAT()

-- Note: only supports the comma separator

-- Note: For DISTINCT and ORDER BY a subquery is required

CREATE OR REPLACE FUNCTION _group_concat(text, text)

RETURNS text AS $$

  SELECT CASE

    WHEN $2 IS NULL THEN $1

    WHEN $1 IS NULL THEN $2

    ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2

  END

$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat (

    BASETYPE = text,

    SFUNC = _group_concat,

    STYPE = text

);

 

2021-09-24 16:25:41    77    0    0

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

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

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

 

1 -- BIT_COUNT()

CREATE OR REPLACE FUNCTION bit_count(bigint)

RETURNS integer AS $$

  SELECT pg_catalog.length(pg_catalog.replace(pg_catalog.ltrim(pg_catalog.textin(pg_catalog.bit_out($1::bit(64))), '0'), '0', ''));

$$ IMMUTABLE STRICT LANGUAGE SQL;

2021-09-24 16:23:20    38    0    0

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 $$

  SELE

2021-09-24 16:21:52    44    0    0

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

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

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

 

1 -- IF

-- Warning: still requires casts in some instances

CREATE OR REPLACE FUNCTION if(boolean, anyelement, anyelement)

RETURNS anyelement AS '

  SELECT CASE WHEN $1 THEN $2 ELSE $3 END

' IMMUTABLE LANGUAGE SQL;

 

2 -- IFNULL

-- Warning: still requires casts in some instances

CREATE OR REPLACE FUNCTION ifnull(anyelement, anyelement)

RETURNS anyelement AS '

  SELECT COALESCE($1, $2)

' IMMUTABLE LANGUAGE SQL;

2021-09-24 16:12:59    33    0    0

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 $$

 

2021-09-24 16:08:29    35    0    0

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

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

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

 

1 -- BENCHMARK()

-- Note: This version requires the expression to be quoted.

-- Note: To show query times in psql, run \timing first

-- Example: SELECT BENCHMARK(100000, $$ length('hello') $$);

CREATE OR REPLACE FUNCTION benchmark(integer, text)

RETURNS integer AS $$

  BEGIN

    FOR i IN 1..$1 LOOP

      EXECUTE 'SELECT ' || $2;

    END LOOP;

    RETURN 0;

  END;

$$ STRICT LANGUAGE PLPGSQL;

 

2 -- CHARSET()

-- This is a bit dodgy as it just returns the database encoding

CREATE OR REPLACE FUNCTION charset(text)

RETURNS text AS $$

  SELECT pg_catalog.lower(setting) from pg_catalog.pg_settings where name='server_encoding'

$$ IMMUTABLE LANGUAGE SQL;

 

3 -- COERCIBILITY()

-- This is a bit dodgy as PostgreSQL does not support collations

-- Note: This is MySQL 5.0 compatible

CREATE OR REPLACE FUNCTION coercibility(name)

RETURNS integer AS $$

  SELECT 3

$$ IMMUTABLE LANGUAGE SQL;

CREATE OR REPLACE FUNCTION coercibility(t

2021-09-24 15:55:51    29    0    0

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, 100688814

2021-09-24 15:50:04    28    0    0

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

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

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

 

1 -- <=> NULL SAFE COMPARISON

-- Note: needs casts in some circumstances

CREATE OR REPLACE FUNCTION _null_safe_cmp(anyelement, anyelement)

RETURNS boolean AS '

  SELECT NOT ($1 IS DISTINCT FROM $2)

' IMMUTABLE LANGUAGE SQL;

CREATE OPERATOR <=> (

  PROCEDURE = _null_safe_cmp,

  LEFTARG = anyelement,

  RIGHTARG = anyelement

);

 

2 -- &&

-- XXX: MySQL version has wacky null behaviour

CREATE FUNCTION _and(booleanboolean)

RETURNS boolean AS $$

  SELECT $1 AND $2

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE OPERATOR && (

  leftarg = boolean,

  rightarg = boolean,

  procedure = _and,

  commutator = &&

);

 

3 -- ||

-- XXX: MySQL version has wacky null behaviour

-- This replaces the SQL standard || concatenation operator

CREATE FUNCTION _or(booleanboolean)

RETURNS boolean AS $$

  SELECT $1 OR $2

$$ IMMUTABLE STRICT LANGUAGE SQL;

CREATE OPERATOR || (

  leftarg = boolean,

  rightarg = boolean,

  procedure = _or,

  commutator = ||

);

2021-09-24 15:42:46    36    0    0

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

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

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

 

1 -- INET_ATON()

-- Credit: Michael Fuhr

CREATE OR REPLACE FUNCTION inet_aton(text)

RETURNS bigint AS $$

  DECLARE

      a text[];

      b text[4];

      up int;

      family int;

      i int;

  BEGIN

      IF position(':' in $1) > 0 THEN

        family = 6;

      ELSE

        family = 4;

      END IF;

      -- mysql doesn't support IPv6 yet, it seems

      IF family = 6 THEN

        RETURN NULL;

      END IF;

      a = pg_catalog.string_to_array($1, '.');

      up = array_upper(a, 1);

      IF up = 4 THEN

        -- nothing to do

        b = a;

      ELSIF up = 3 THEN

        -- 127.1.2 = 127.1.0.2

        b = array[a[1], a[2], '0', a[3]];

      ELSIF up = 2 THEN

        -- 127.1 = 127.0.0.1

        b = array[a[1], '0', '0', a[2]];

      ELSIF up = 1 THEN

        -- 127 = 0.0.0.127

        b = array['0', '0', '0', a[1]];

      END IF;

      i = 1;

      -- handle 127..1

      WHILE i <= 4 LOOP

        IF length(b[i]) = 0

2/3