bitner

CREATE OR REPLACE FUNCTION public.geocode_google(IN inaddress text, OUT address text, OUT longitude double precision, OUT latitude double precision)
  RETURNS record AS
$BODY$ 
from geopy.geocoders import GoogleV3
geolocator = GoogleV3()
try:
  address, (latitude, longitude) = geolocator.geocode(inaddress,timeout=1,exactly_one=True)
  return address, longitude, latitude
except:
  return None, None, None

Kartones

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

• -E: will describe the underlaying queries of the \ commands (cool for learning!)
• -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

happysundar

DROP FUNCTION IF EXISTS get_role_to_actor_and_actor_to_role( INOUT BIGINT, OUT JSONB, OUT JSONB );
CREATE OR REPLACE FUNCTION get_role_to_actor_and_actor_to_role(
    INOUT program_id    BIGINT,
    OUT   actor_to_role JSONB,
    OUT   role_to_actor JSONB)
    RETURNS RECORD IMMUTABLE
AS $plpython_function$
import json

aanari

CREATE FUNCTION batch_at_will() RETURNS INTEGER LANGUAGE plpgsql AS $$
DECLARE batched_count INTEGER = 1;
BEGIN
    WITH selected_users AS (
        SELECT id
        FROM users
        WHERE role = 'moderator'
        AND registration_date < CURRENT_DATE - INTERVAL '4' YEAR
        LIMIT 1000
        FOR UPDATE NOWAIT

vehrka

-- Function: makegrid_epsg3857(text, text, text, numeric)

-- DROP FUNCTION makegrid_epsg3857(text, text, text, numeric);

CREATE OR REPLACE FUNCTION makegrid_epsg3857(schemaname text, boundingbox text, gridtable text, halfwidth numeric)
  RETURNS text AS
$BODY$
    DECLARE
    tbl_cnt int;
    XMIN numeric;

rominf

CREATE OR REPLACE FUNCTION crop(image bytea, rect box)
 RETURNS bytea
 LANGUAGE plpythonu
AS $function$
if ('io' in SD) and ('StringIO' in SD) and ('Image' in SD):
    io = SD['io']
    StringIO = SD['StringIO']
    Image = SD['Image']
else:
    import io, StringIO

andersontep

"""
* requires usaddress https://github.com/datamade/usaddress
* check out the website! https://parserator.datamade.us/usaddress

The usaddress package is pretty great for normalizing inconsistent address data,
especially when if you have a lot to process and can't rely on using a geocoding api.
The results are really granular, probably moreso than you'll need, and definitely
more than most CRM systems if integrating these addresses is your goal. 

This is just a simple wrapper around the usaddress.tag() function that I feel will 

shahifaqeer

from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from BeautifulSoup import BeautifulSoup
import time

NAME = "Mom"
RELOAD_TIMEOUT = 1
ERROR_TIMEOUT = 5
NUM_CYCLES = 10
FIREFOX_PATH = "/Users/sgrover/Library/Application Support/Firefox/Profiles/9w4h7jdq.default"

Jan-Zeiseweis

create or replace function f_sk_date (ts timestamp )
  returns integer
  stable as $$
    if not ts:
      return None
    return int(str(ts)[0:10].replace('-',''))
  $$ language plpythonu;

create or replace function f_date (ts timestamp)
  returns date

sthum

--- If you are missing the pypythonu extension, install it first
--- (Ubuntu): sudo apt-get install python-psycopg2

--- Activate plpython 
CREATE EXTENSION plpythonu;

--- Create the profiling function
CREATE OR REPLACE FUNCTION similarity(text, text) RETURNS numeric AS $$
import difflib
return difflib.SequenceMatcher(None,args[0], args[1]).ratio()