brito

/*
  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'

brito

-- 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),

brito

// 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 = 

brito

/*
  Flatten JSON tables
*/
create or replace function flatten_json(data jsonb)
    returns table (
        key       text,
        value     jsonb,
        json_type text)
as
$$

brito

  /**
   * 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

brito

-- 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);

brito

/* 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)

brito

/*
  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])
}

brito

/*       __ _                              _             _                           __      
   __ _ / _| |_ ___   __ _ _ __   __ _ ___| |_ ___   ___| |__   __ _ ___ _ __ ___   /_/  ___ 
  / _` | |_| __/ _ \ / _` | '_ \ / _` / __| __/ _ \ / __| '_ \ / _` / __| '_ ` _ \ / _ \/ __|
 | (_| |  _| || (_) | (_| | | | | (_| \__ \ || (_) | (__| | | | (_| \__ \ | | | | | (_) \__ \
  \__,_|_|  \__\___/ \__,_|_| |_|\__,_|___/\__\___/ \___|_| |_|\__,_|___/_| |_| |_|\___/|___/ 
  αυτοαναστοχασμός ergo legit */

select
  _column, _type,
  _nulls,

brito

/*
  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;