liquidgenius’s gists

liquidgenius / postgres-fx-functions.sql

Created August 8, 2020 15:03 — forked from jasher/postgres-fx-functions.sql

A set of general utility functions for Postgres (pgSQL); the antithesis of functional programming.

	-- ********************************************************************************************
	-- General FX utility functions in PL/pgSql (Postgres)
	-- Released under the MIT license: http://opensource.org/licenses/mit-license.php by Jon Asher
	-- Download current source at https://gist.github.com/jasher/6026284
	-- ********************************************************************************************

	CREATE FUNCTION array_contains(p_int_arr integer[], p_value integer) RETURNS boolean
	LANGUAGE plpgsql
	AS $$
	BEGIN

liquidgenius / postgres_timestamp_functions.sql

Created August 8, 2020 15:00 — forked from EddyBorja/postgres_timestamp_functions.sql

Convenience methods for some commonly used timestamps in analytics or reporting.

	CREATE OR REPLACE FUNCTION day_begin(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
	RETURNS timestamp with time zone AS $$
	BEGIN
	RETURN date_trunc('day', dt);
	END;
	$$ LANGUAGE plpgsql;

	CREATE OR REPLACE FUNCTION day_end(dt timestamp with time zone DEFAULT CURRENT_TIMESTAMP)
	RETURNS timestamp with time zone AS $$
	BEGIN

liquidgenius / py_environ.sql

Created August 8, 2020 14:43 — forked from davidradernj/py_environ.sql

plpython to read environment variable from PostgreSQL

	create extension plpythonu;

	create type py_environ_type as (name text, value text);

	create or replace function py_environ(name varchar DEFAULT NULL)
	returns setof py_environ_type
	as $$
	import os
	aev = []
	if name is None:

liquidgenius / date_user_defined_functions_redshift.sql

Created August 8, 2020 14:40 — forked from Jan-Zeiseweis/date_user_defined_functions_redshift.sql

User defined python functions for date dimensions in redshift

	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

liquidgenius / plpythonu tips

Created August 8, 2020 14:39 — forked from recurse-id/plpythonu tips

Some tips on creating pl/python functions in psql

	1) instead of returning SETOF composite_type or SETOF RECORD (OUT col type) return TABLE (col type,etc) and just yield.

	2) For long queries in plpy.execute use triple quotes for multiline queries

	3) use explain [query] to figure out what is taking so much time on a query then build indexes to remove the "sequential scans"

liquidgenius / points_contains_query_plpythonu.psql

Created August 8, 2020 14:35 — forked from beyoung/points_contains_query_plpythonu.psql

	/**
	* 传入轨迹抽样点，获取整条轨迹穿越地区
	* 300: 执行时间184.992ms
	* 1000: 执行时间587.400ms
	* 5000: 执行时间2834.339ms
	* 10000:执行时间5526.228ms
	*/
	CREATE OR REPLACE FUNCTION getAddressFromPoints(points_str text)
	RETURNS text AS $$
	import json

liquidgenius / postgresql_crop_image.sql

Created August 8, 2020 14:30 — forked from rominf/postgresql_crop_image.sql

PostgreSQL function to crop images (uses plpythonu and python PIL)

	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

liquidgenius / plpython - geopy geocode

Created August 8, 2020 14:29 — forked from bitner/plpython - geopy geocode

	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

liquidgenius / psql_text_similaritry_operator.sql

Created August 7, 2020 23:08 — forked from sthum/psql_text_similaritry_operator.sql

Pragmatic solution for calculating the similarity of text / words of database entires in PostgreSQL

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

liquidgenius / plpython_returning_record.py

Created August 7, 2020 23:05 — forked from happysundar/plpython_returning_record.py

Example of a PLPythonU method that returns a record

	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

Liquidgenius liquidgenius