PostgreSQL 数组函数扩展

函数定义

drop function if exists array_repeat(bigint[]);
drop function if exists array_repeat(text[]);
drop function if exists array_different(bigint[],bigint[]);
drop function if exists array_different(text[],text[]);
drop function if exists array_same(bigint[],bigint[]);
drop function if exists array_same(text[],text[]);

/****************************************************************************************
    数组去除重复的值,去重后按升序排序
    select * from array_repeat(array[1,1,2,2,2,3])
    {1,2,3}
drop function if exists array_repeat(bigint[]);
drop function if exists array_repeat(text[]);
****************************************************************************************/
create or replace function array_repeat(bigint[])
  returns table(val bigint[])
as $$
    with first as(
		select val from unnest($1) as val
	),second as(
		select val from first group by val order by val
	)select array_agg(val) from second
$$ language sql strict immutable;

create or replace function array_repeat(text[])
  returns table(val text[])
as $$
    with first as(
		select val from unnest($1) as val
	),second as(
		select val from first group by val order by val
	)select array_agg(val) from second
$$ language sql strict immutable;

/****************************************************************************************
    求两个数组的差异部份,去除共同部份后按升序排序
    select * from array_different(array[1,2,3,4,5,6,7,8,9,10]::bigint[],array[5,6,7,8,9,10,11,12,13,14,15]::bigint[])
    {1,2,3,4,11,12,15,13,14}
drop function if exists array_different(bigint[],bigint[]);
drop function if exists array_different(text[],text[]);
****************************************************************************************/
create or replace function array_different(bigint[],bigint[])
  returns table(val bigint[])
as $$
    with first as(
		select val from unnest($1) as val
	),second as(
		select val from unnest($2) as val
	),different as(
		select unnest(array_agg(first.val) || array_agg(second.val)) as val
			from first full outer join second on first.val=second.val 
				where first.val is null or second.val is null order by val
	) select array_agg(val) from different where val is not null
$$ language sql strict immutable;

create or replace function array_different(text[],text[])
  returns table(val text[])
as $$
    with first as(
		select val from unnest($1) as val
	),second as(
		select val from unnest($2) as val
	),different as(
		select unnest(array_agg(first.val) || array_agg(second.val)) as val
			from first full outer join second on first.val=second.val 
				where first.val is null or second.val is null order by val
	) select array_agg(val) from different where val is not null
$$ language sql strict immutable;

/****************************************************************************************
    求两个数组的相同部份,去除差异部份后按升序排序
    select * from array_same(array[1,2,3,4,5,6,7,8,9,10]::bigint[],array[5,6,7,8,9,10,11,12,13,14,15]::bigint[])
    {5,6,7,8,9,10}
drop function if exists array_same(bigint[],bigint[]);
drop function if exists array_same(text[],text[]);
****************************************************************************************/
create or replace function array_same(bigint[],bigint[])
  returns table(val bigint[])
as $$
    with first as(
		select val from unnest($1) as val
	),second as(
		select val from unnest($2) as val
	),same as(
		select unnest(array_agg(first.val) || array_agg(second.val)) as val
			from first inner join second on first.val=second.val
	),merges as(
		 select val from same group by val order by val
	) select array_agg(val) from merges
$$ language sql strict immutable;


create or replace function array_same(text[],text[])
  returns table(val text[])
as $$
   with first as(
		select val from unnest($1) as val
	),second as(
		select val from unnest($2) as val
	),same as(
		select unnest(array_agg(first.val) || array_agg(second.val)) as val
			from first inner join second on first.val=second.val
	),merges as(
		 select val from same group by val order by val
	) select array_agg(val) from merges
$$ language sql strict immutable;

postgresql数组操作符等于和不等于要求两个数组的元素值和值的位置必须完全一致,因此下面的方法都返回false

select array[1,2,3]::bigint[]=array[3,2,1]::bigint[]
select array[1,2,3]::bigint[]<>array[3,2,1]::bigint[]

值和值的位置必须完全相同返回ture

select array[1,2,3]::bigint[]=array[1,2,3]::bigint[]
select array[1,2,3]::bigint[]<>array[1,2,3]::bigint[]

重要的事情说3遍,避免大家填坑
postgresql数组操作符等于和不等于要求元素值和位置必须完全相同
postgresql数组操作符等于和不等于要求元素值和位置必须完全相同
postgresql数组操作符等于和不等于要求元素值和位置必须完全相同