postgres jsonb_diff and array_unique

 I recently copypasted a couple of functions from stackoverflow for my prostgres projects that are worth mentioning, cause they show a pattern on how to write plpsql functions:

	-- select internal.array_unique(array['foo','bar'] || array['foo'])
	create or replace function array_unique (a text[]) returns text[] as $$
	select array (
	select distinct v from unnest(a) as b(v)
	)
	$$ language sql;
	-- Example usage:
	-- select ts, op, jsonb_pretty(mb_jsonb_diff(record, old_record))
	-- from audit.record_version
	-- where table_name='subscription'
	-- and (record->'id')::int=1 order by ts;
	create or replace function mb_jsonb_diff(record jsonb, old_record jsonb)
	returns jsonb
	language plpgsql
	as
	$$
	declare
	ret jsonb;
	begin
	SELECT jsonb_object_agg(a.key, a.value)
	into ret
	FROM ( SELECT key, value FROM jsonb_each(record)) a LEFT OUTER JOIN
	( SELECT key, value FROM jsonb_each(old_record)) b ON a.key = b.key
	WHERE a.value != b.value OR b.key IS NULL;
	return ret;
	end;
	$$

view raw utils.sql hosted with ❤ by GitHub