Skip to content

Instantly share code, notes, and snippets.

View happysundar's full-sized avatar

Sundar Rajagopalan happysundar

1 follower · 4 following
View GitHub Profile
@happysundar
happysundar / cleanup_schemas.sql
Created October 23, 2014 23:40
posgresql function to cleanup the schemas within a database
DROP FUNCTION IF EXISTS cleanup( ) CASCADE;
CREATE OR REPLACE FUNCTION
cleanup()
RETURNS VOID
AS $$
DECLARE
row RECORD ;
BEGIN
FOR row IN WITH T1 AS (SELECT nspname :: TEXT AS schema_name
FROM pg_catalog.pg_namespace), T2 AS (SELECT schema_name
@happysundar
happysundar / get_column_names
Created October 19, 2014 18:42
Get all the column names of a table...
DROP FUNCTION IF EXISTS get_column_names_csv( TEXT, TEXT ) CASCADE;
CREATE OR REPLACE FUNCTION
get_column_names_csv(input_schema_name TEXT, input_table_name TEXT)
RETURNS SETOF TEXT IMMUTABLE
AS $$
BEGIN
RETURN QUERY
WITH T1 AS (
SELECT column_name
FROM rovi.information_schema.columns
@happysundar
happysundar / JSONB to array
Created August 22, 2014 23:34
how to extract an array out of JSONB in postgres
select array_agg(value) from jsonb_each_text('{"3d": "2011-05-20", "wide": "2011-05-20", "imax_3d": "2011-05-20"}'::jsonb);
@happysundar
happysundar / install_python.yaml
Created August 7, 2014 22:31
playbook to get virtualenv + pythoz installed in a Centos box
---
- hosts: all
sudo: yes
tasks:
- name: install the EPEL and remi repos
yum: name={{item}}
state=installed
with_items:
- http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
- http://rpms.famillecollet.com/enterprise/remi-release-6.rpm
@happysundar
happysundar / log_from_plpgsql.sql
Created July 10, 2014 03:40
From http://www.depesz.com/2011/05/06/understanding-postgresql-conf-log/
RAISE LOG 'Calculated income for user % is %', user.id, user.income;
@happysundar
happysundar / drop_all_tables.sql
Created July 8, 2014 05:48
From http://stackoverflow.com/questions/3327312/drop-all-tables-in-postgresql PL/PgSQL Function to drop all tables
DROP FUNCTION IF EXISTS remove_all();
CREATE FUNCTION remove_all() RETURNS void AS $$
DECLARE
rec RECORD;
cmd text;
BEGIN
cmd := '';
FOR rec IN SELECT
@happysundar
happysundar / filter_non_null_elems_from_array.sql
Created June 27, 2014 17:58
filtering out all non-null elements from an array in postgresql
WITH T1 AS (
SELECT
program_id,
ARRAY [alias_title,
alias_title_2,
alias_title_3,
alias_title_4] AS title_array
FROM movies
),
T2 AS (
@happysundar
happysundar / .vimrc
Created June 19, 2014 04:36
vimrc
set nocompatible " must be the first line
filetype on
filetype indent on
filetype plugin on
set laststatus=2
syntax on
set cursorline
set statusline=%<%f\%h%m%r%=%-20.(line=%l\ \ col=%c%V\ \ totlin=%L%)\ \ \%h%m%r%=%-40(bytval=0x%B,%n%Y%)\%P
" Allow saving of files as sudo when I forgot to start vim using sudo.
cmap w!! w !sudo tee > /dev/null %
@happysundar
happysundar / create_movies_table.sql
Created June 14, 2014 20:06
Creating a plpgsql function
DROP FUNCTION IF EXISTS get_image_records( BIGINT ) CASCADE;
CREATE OR REPLACE FUNCTION
get_image_records(input_program_id BIGINT)
RETURNS SETOF JSON STABLE
AS $$
BEGIN
RETURN QUERY
WITH T2 AS (
SELECT
file_url :: TEXT,
@happysundar
happysundar / postgres_backup_restore.sh
Created May 15, 2014 03:31
Commands to backup and restore postgres database
/usr/pgsql-9.3/bin/pg_dump -U <user_name> -d <db_name> -f <backup_file>.sql -h <db_host>
/usr/pgsql-9.3/bin/psql -U <user_name> -d <db_name> -f <backup_file>.sql -h <db_host>