the self-concatenating table is entertaining but a bad idea; will remove eventually

👋 Hi

💻 I'm a longtime Mac user (macOS=unix) and command line aficiando.

😮 I started programming with an Apple ][e in the early 1980s.

🔧 I wrangle data with basic Unix commands, shell scripts, PostgreSQL, Python, and other open source software.

✏️ I use git and GitHub Pages to keep and share notes about the software I use.

❤️ I enjoy writing about technology and inevitably learn a lot in the process.

☕️ You're not really interested in my hobbies or personal life, are you?

🔖 "So it goes."

— PostgreSqlStan

	CREATE FUNCTION tf_fix_multiline_json()
	RETURNS TRIGGER AS $TF$
	-- ⚠️ probably bad idea, but had to try it
	-- will likely remove (soon) and perform in procedure instead
	-- conditionally remove linefeeds from multiline json
	DECLARE
	is_json_rows BOOL;
	BEGIN
	IF ((SELECT COUNT(*) FROM i_text LIMIT 2) > 1) THEN
	WITH a AS (SELECT distinct(left(t,1) \|\| right(t,1)) AS da
	FROM i_text LIMIT 5)
	SELECT (SELECT count(*) FROM a)=1 AND (SELECT TRUE FROM a WHERE da='{}')
	INTO is_json_rows;
	-- don't concatenate to single row if it's rows of valid JSON
	IF NOT is_json_rows THEN
	CREATE TEMPORARY TABLE fix(t TEXT);
	INSERT INTO fix SELECT string_agg(t, ' ') FROM i_text;
	DELETE FROM i_text;
	INSERT INTO i_text SELECT * FROM fix;
	DROP TABLE fix;
	END IF;
	END IF;
	RETURN NEW;
	END;
	$TF$ LANGUAGE plpgsql;