bitdivine

CREATE VIEW inf_vacuum AS
SELECT                            
    schemaname, relname,
    last_vacuum, last_autovacuum,
    vacuum_count, autovacuum_count FROM pg_stat_user_tables;

bitdivine

CREATE VIEW inf_locks AS
SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtidi, usename, pid, granted, query
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database left join pg_stat_activity using (pid) WHERE (db.datname = 'blue' OR db.datname IS NULL)
AND NOT pid = pg_backend_pid();

bitdivine

-- Capped collection of JSON blobs: (Use json for postgres 9.4 and below and jsonb for 9.5 and above)
CREATE SEQUENCE circle_index START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 5 CACHE 1 CYCLE ;
CREATE TABLE circle ( i integer PRIMARY KEY default nextval('circle_index') NOT NULL, tim timestamp DEFAULT current_timestamp NOT NULL, dat jsonb );

INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":1}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":2}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":3}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSERT INTO circle(i, dat) SELECT nextval('circle_index') as idx, '{"a":12345,"b":4}' AS val ON CONFLICT (i) DO UPDATE SET i=EXCLUDED.i, tim=DEFAULT, dat=EXCLUDED.dat;
INSER

bitdivine

#!/usr/bin/gawk {system("my command here " FILENAME); exit}

bitdivine

CREATE OR REPLACE FUNCTION weighted_stddev_state(state numeric[], val numeric, weight numeric) RETURNS numeric[3] AS
$$
BEGIN
        IF weight IS NULL OR val IS NULL
        THEN RETURN state;
        ELSE RETURN ARRAY[state[1]+weight, state[2]+val*weight, state[3]+val^2*weight];
        END IF;
END;
$$
LANGUAGE plpgsql;

bitdivine

function formatTimeInterval(seconds){
    seconds = Number(seconds);
    return [60,60,Infinity].map((max) => { var part = seconds % max; seconds -= part; seconds /= max; return part; }).reverse().map((n)=>('00'+n).replace(/^0*([0-9][0-9])/,(m,g1)=>g1).replace(/([.]...).*/,(m,g)=>g)).join(':');
}

bitdivine

CREATE OR REPLACE VIEW inf_table_sizes AS
SELECT relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size",
    tablename
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  LEFT JOIN (select tab.relname as tablename,toast.relname as toastname from pg_class as tab join pg_class as toast on (toast.oid = tab.reltoastrelid)) AS toast ON (relname=toastname)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
;

bitdivine

select tab.relname as table,toast.relname as toast from pg_class as tab join pg_class as toast on (toast.oid = tab.reltoastrelid);

bitdivine

(function(){
	// Input field given to n decimal places.  Degrades gracefully to standard numeric input.
	// Usage: <input type="number" is="decimal-number" data-places="2" name="..." value="...">
	try {
	var proto = Object.create(HTMLInputElement.prototype);
	proto.createdCallback = function() {
		this.type = "number";
		this.addEventListener("change", this.decimate);
		this.value = parseFloat(this.value).toFixed(this.getAttribute('data-places'));
	};

bitdivine

// Usage: d = new Date(); sqldate = _u(d); // 2016-02-14
function _02(s){return ('00'+s).slice(-2);}
function _u(d){ return [d.getUTCFullYear(),_02(d.getUTCMonth()+1),_02(d.getUTCDate())].map(String).join('-'); }