robertsosinski

function parse_git_branch { 
   git branch --no-color 2> /dev/null | sed -e '/^[^*]/d' -e 's/* \(.*\)/(\1)/' 
} 
export PS1="[\t] \u@\h:\W\$(parse_git_branch)$ "

robertsosinski

CREATE FUNCTION xor(bool, bool) RETURNS bool AS 'SELECT ($1 AND NOT $2) OR (NOT $1 AND $2);' LANGUAGE 'sql';
CREATE OPERATOR ~| (PROCEDURE='xor', LEFTARG=bool, RIGHTARG=bool);

CREATE FUNCTION condition(bool, bool) RETURNS bool AS 'SELECT CASE WHEN $1 AND NOT $2 THEN false ELSE true END;' LANGUAGE 'sql';
CREATE OPERATOR -> (PROCEDURE='condition', LEFTARG=bool, RIGHTARG=bool);

CREATE FUNCTION bicondition(bool, bool) RETURNS bool AS 'SELECT CASE WHEN ($1 AND $2) OR (NOT $1 and NOT $2) THEN true ELSE false END;' LANGUAGE 'sql';
CREATE OPERATOR <-> (PROCEDURE='bicondition', LEFTARG=bool, RIGHTARG=bool);

robertsosinski

ProductType.facet(:name).all
 [{:name=>"Direct Receiver", :count=>9},
  {:name=>"Input System", :count=>6},
  {:name=>"Performance Compressor", :count=>6},
  {:name=>"Air Controller", :count=>5},
  {:name=>"Audible Mount", :count=>5},
  {:name=>"Auto Amplifier", :count=>5},
  {:name=>"Digital Component", :count=>5},
  {:name=>"Direct Transmitter", :count=>5},
  {:name=>"Gel Receiver", :count=>5}...

robertsosinski

# Color Pry prompt according to environment
old_prompt = Pry.config.prompt

color = {
  :red    => "31m",
  :green  => "32m",
  :yellow => "33m",
  :blue   => "34m",
  :purple => "35m",
  :cyan   => "36m"

robertsosinski

namespace :db do
  desc "Migrates the database to the target version, or to the lastest version if no target is given"
  task :migrate, [:target, :current] => :environment do |t, args|
    opts = {}
    opts[:target]  = args[:target].to_i  if args[:target]
    opts[:current] = args[:current].to_i if args[:current]

    Sequel::Migrator.run(DB, "db/migrate", opts)
    Rake::Task["db:dump"].invoke if Rails.env.development?
  end

robertsosinski

create trigger trades_readonly_trigger before insert or update or delete or truncate on trades for each statement execute procedure readonly_trigger_function();

robertsosinski

with
blocked as (
  select 
    pl.transactionid,
    pl.pid blocked_pid, pl.mode blocked_mode,
    psa.usename blocked_user, psa.application_name blocked_application, 
    now() - psa.query_start blocked_duration, psa.xact_start blocked_xact_start, psa.query_start blocked_query_start,
    psa.current_query blocked_statement
  from pg_stat_activity psa
  inner join pg_locks pl on pl.pid = psa.procpid

robertsosinski

class PostgresSessionStore < ActiveRecord::SessionStore::SqlBypass
  @@session_id_column = 'id'
  @@schema_name = 'system'
  cattr_accessor :schema_name

  class << self
    def marshal(data)
      ::Base64.encode64(Marshal.dump(data)).chomp if data
    end

robertsosinski

create or replace function set_keyval(tablename varchar, keyname varchar, valuestring text, schemaname varchar default 'public') returns table(key varchar, value text, created_at timestamp, updated_at timestamp) as $$
begin
  return query execute 'update '||quote_ident(schemaname)||'.'||quote_ident(tablename)||' set value = $2, updated_at = now() where key = $1 returning *' using keyname, valuestring;
  if not found then
    return query execute 'insert into '||quote_ident(schemaname)||'.'||quote_ident(tablename)||' (key, value, created_at, updated_at) values ($1, $2, now(), now()) returning *' using keyname, valuestring;
  end if;
end;
$$ language 'plpgsql';

create or replace function get_keyval(tablename varchar, keyname varchar, schemaname varchar default 'public') returns table(key varchar, value text, created_at timestamp, updated_at timestamp) as $$

robertsosinski

SELECT c.id, c.name, c.jan, c.feb, c.mar
FROM crosstab(
  'select buyers.id, buyers.name, transactions.month, sum(transactions.amount) from transactions inner join buyers on buyers.id = transactions.buyer_id group by buyers.id, buyers.name, transactions.month order by buyers.id, transactions.month',
  'select generate_series(1, 3)'
) c (id integer, name character varying, jan numeric(12,2), feb numeric(12,2), mar numeric(12,2));