This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
List all schemas, tables, and columns | |
*/ | |
create view inventory.index as | |
select nspname as _schema, | |
relname as _relname, | |
case | |
when relkind = 'r' then 'table' | |
when relkind = 'v' then 'view' | |
when relkind = 'm' then 'materialized view' |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- TEST ts_rank functions and (some) configs | |
select * | |
from (select trim(regexp_replace(txt, E'\\s*\n\\s*', ' ', 'g')) as txt, tsv | |
from (values ('my phone will not make or receive calls'), | |
($$ no matter what I do, my phone will not make or receive | |
calls even when I've asked nicely and it all goes well in the day | |
and then one day I did this other unrelated thing | |
and it still wouldn't work AT ALL so I asked Ben | |
who probably doesn't either but he has interesting answers $$), | |
('call my phone')) test_values (txt), |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// Voice Driven Web Apps | |
//developer.chrome.com/blog/voice-driven-web-apps-introduction-to-the-web-speech-api | |
with (new webkitSpeechRecognition) | |
continuous = | |
interimResults = | |
true, | |
onstart = | |
onerror = |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Flatten JSON tables | |
*/ | |
create or replace function flatten_json(data jsonb) | |
returns table ( | |
key text, | |
value jsonb, | |
json_type text) | |
as | |
$$ |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Flatten an Object into a table with rows of columns including keys as headers | |
* @param object to flatten | |
*/ | |
function _asTable (object) { | |
return [].concat(object).reduce((list, item, i) => { | |
let labels = Object.keys(item) | |
if (!i && +labels[0] !== 0) list.push(labels) | |
list.push(labels.map(label => item[label])) | |
return list |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- since it uses dblink it should be enabled in the database. | |
-- CREATE EXTENSION dblink; | |
-- And you'll may need to grant permissions to use it to your user. | |
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text) TO user; | |
-- GRANT EXECUTE ON FUNCTION dblink_connect_u(text, text) TO user; | |
-- Usage example: | |
-- select g_parsel('insert into osm_polygon_extra select osm_id, st_pointonsurface( st_collect( geom ) ) from osm_polygons group by osm_id;', 'osm_polygons', 12); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Tally v 1.0 */ | |
function tally (range, limit = 10){ | |
let total = range.length, | |
values = {} | |
for (let [k, v] of range) { | |
k = ('' + k).replace(/\n[\s\S]*/, '…') | |
values[k] = values[k] ? values[k] + 1 : 1 | |
} | |
return Object.entries(values) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Transform a string using multiple regex | |
@customfunction | |
*/ | |
function replaces(operators,operands) { | |
return operators[0].map ? | |
operators.reduce((a,b) => replaces(b,a), operands) | |
: operands.replace(new RegExp(operators[0]), operators[1]) | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* __ _ _ _ __ | |
__ _ / _| |_ ___ __ _ _ __ __ _ ___| |_ ___ ___| |__ __ _ ___ _ __ ___ /_/ ___ | |
/ _` | |_| __/ _ \ / _` | '_ \ / _` / __| __/ _ \ / __| '_ \ / _` / __| '_ ` _ \ / _ \/ __| | |
| (_| | _| || (_) | (_| | | | | (_| \__ \ || (_) | (__| | | | (_| \__ \ | | | | | (_) \__ \ | |
\__,_|_| \__\___/ \__,_|_| |_|\__,_|___/\__\___/ \___|_| |_|\__,_|___/_| |_| |_|\___/|___/ | |
αυτοαναστοχασμός ergo legit */ | |
select | |
_column, _type, | |
_nulls, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
Simple ASCII bar visualization of a single quantity | |
eg */ select * from _bar(53); /* | |
*/ | |
create function _bar(_quantity int, _glyph text = '■') | |
returns text as $$ | |
select repeat(_glyph,_quantity/3) || ' ' || _quantity | |
$$ language sql immutable; |
NewerOlder