pg>mysql>aggregate.sql
2021-09-24 16:30:20    38    0    0
ivan

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

);

 

Pre: pg>mysql>substring_index

Next: pg>mysql>bit.sql

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