得到交集, $2为真子集则返回true

  1. create or replace function array_intersect(anyarray, anyarray)
  2. returns anyarray
  3. immutable
  4. language sql
  5. as $function$
  6. select array(
  7.   select unnest($1)
  8.   intersect
  9.   select unnest($2)
  10. );
  11. $function$;
  13. create or replace function array_intersect(text[], text)
  14. returns anyarray
  15. immutable
  16. language sql
  17. as $function$
  18. select  array_intersect($1,string_to_array($2,','))
  19. $function$;
  21. create or replace function array_intersect(int[], text)
  22. returns anyarray
  23. immutable
  24. language sql
  25. as $function$
  26. select  array_intersect($1,string_to_array($2,',')::int[])
  27. $function$;
  29. create or replace function array_intersect(bigint[], text)
  30. returns anyarray
  31. immutable
  32. language sql
  33. as $function$
  34. select  array_intersect($1,string_to_array($2,',')::bigint[])
  35. $function$;
  37. create or replace function array_has_intersection(anyarray, anyarray)
  38. returns bool
  39. immutable
  40. language sql
  41. as $function$
  42. select true from (
  43.   select unnest($1)
  44.   intersect
  45.   select unnest($2)
  46. ) t limit 1;
  47. $function$;
  50. create or replace function array_has_intersection(bigint[], text[])
  51. returns bool
  52. immutable
  53. language sql
  54. as $function$
  55. select array_has_intersection($1,$2::bigint[])
  56. $function$;
  58. create or replace function array_has_intersection(bigint[], int[])
  59. returns bool
  60. immutable
  61. language sql
  62. as $function$
  63. select array_has_intersection($1,$2::bigint[])
  64. $function$;
  67. create or replace function array_has_intersection(int[], text[])
  68. returns bool
  69. immutable
  70. language sql
  71. as $function$
  72. select array_has_intersection($1,$2::int[])
  73. $function$;
  75. create or replace function array_has_intersection(text[], text)
  76. returns bool
  77. immutable
  78. language sql
  79. as $function$
  80. select array_has_intersection($1,string_to_array($2,','))
  81. $function$;
  83. create or replace function array_has_intersection(int[], text)
  84. returns bool
  85. immutable
  86. language sql
  87. as $function$
  88. select array_has_intersection($1,string_to_array($2,',')::int[])
  89. $function$;
  91. create or replace function array_has_intersection(bigint[], text)
  92. returns bool
  93. immutable
  94. language sql
  95. as $function$
  96. select array_has_intersection($1,string_to_array($2,',')::bigint[])
  97. $function$;

差集, 全差集

需要用到不同入参时,仿照上边sql扩展即可

create or replace function array_diffset(anyarray, anyarray)
  returns anyarray
  immutable
  language sql
as $function$
    select array(
        select unnest($1)
        except
        select unnest($2)
    );
$function$;

create or replace function array_diffset_full(anyarray, anyarray)
  returns anyarray
  immutable
  language sql
as $function$
    with t1(n) as (
        select unnest($1)
        except
        select unnest($2)
    ),t2(n) as (
        select unnest($2)
        except
        select unnest($1)
    ) select array(
        select n from t1
          union
        select n from t2
    );
$function$;