A template/pattern (that I commonly use) for creating complex PostgreSQL queries

complex_postres_query_template.sql

/*
 * This is a template for creating very complex PostgreSQL queries.
 * Large queries quickly become very unreadable because variables and
 * other snippets of code cannot be refactored out. This template demos
 * using variables within an anonymous PL/pgSQL block and returning the
 * table result, WITHOUT requiring write permission to the database!
 * The use of variables are trivial within this example.
 */
 
DO $$
	-- declare variables that can be reused in your query
	DECLARE work_size_seconds CONSTANT bigint = (60 * 10);
	DECLARE base_date CONSTANT DATE = '2016-01-15 12:00:00';
	-- mutable variables work too
	DECLARE counter bigint = 0;
BEGIN
	-- any procedural sql can go here
	-- e.g.:
	IF NOW() > base_date THEN
		counter := 1;
	ELSE
		counter := -1;
	END IF;

	-- your output table
	CREATE TEMP TABLE tmp_table ON COMMIT DROP AS
		-- the rest of your complicated query goes here
		SELECT
			*,
			counter AS before_base_date
		FROM some_table
		WHERE some_table.created_at > base_date
	;
END;
$$;

-- COPY is useful when executing query from CLI on server
--COPY (
SELECT *
FROM tmp_table;
--) TO '/tmp/my query.csv' WITH CSV HEADER QUOTE '"';