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

Clojure and aplisms

The more I do APL and K, the more I see forks and hooks everywhere else.

That's just a couple of little helpers I've added in some clojure projects. One is "apply-if", which more than from APL, I'm taking it from any procedural language, where you can do `x= if p(x) then f(x) else x`. We used to do this a lot in lua (using ands and ors). But in a combinator style.

Second one is an attempt at the fork pattern.

Look how the avg looks like. it's verbose, but it captures the idea very close to how APL does it.

	(defn apply-if
	"if(p(m)) then f(m, ...args) else m
	Useful for `maybe-*` like updates."
	[m p f & args]
	(if (p m)
	(apply f m args)
	m))
	(defn fork [l c r]
	#(->> % ((juxt l r)) (apply c)))
	(defn avg [a]
	((fork (partial apply +) / count) a))
	(defn unique? [a]
	((fork count = (comp count set)) a))

view raw util.clj hosted with ❤ by GitHub

2 classes of system designers

Here's a devastating quote from Andy Kinslow (NATO SOFTWARE ENGINEERING CONFERENCE ,1968) 

There are two classes of system designers. The first, if given five problems will solve them one at a time.

The second will come back and announce that these aren’t the real problems, and will eventually propose a solution to the single problem which underlies the original five.

This is the ‘system type’ who is great during the initial stages of a design project. However, you had better get rid of him after the first six months if you want to get a working system.

I found it in the Software Engineering conference pdf , but I didn't know anything about that "Andy Kinslow". And looking for that person, led me to some interesting findings, like an APL paper, or finding out that Andy Kinslow pioneered work on Time-Sharing-System, following leads of Corbato and Strachey

Copy table from another db in postgres

 This one is somehow a followup on "Duplicate row in plain sql". What I needed in this case was to copy 

Both dbs live in the same physical postgres, and the main user has rights over everything, so the process was painless, and took 5 minutes to set it up (accounting for reading the docs).

The trick is to use the postgres_fdw and create a schema "dev" inside db_test, so I can access the tables in db_dev.

	create extension postgres_fdw ;
	create server dev foreign data wrapper postgres_fdw options (host 'localhost', dbname 'db_dev');
	select user;
	create user mapping for my_user server dev options (user 'my_user' );
	create schema dev;
	import foreign schema public limit to ( table_to_copy ) from server dev into dev;
	select * from dev.table_to_copy;

view raw postgres_fdw.sql hosted with ❤ by GitHub

Now you can easily do your  `insert into table_to_copy select * from dev.table_to_copy;`