liquidgenius

create function customer_search_email_insert()
   returns trigger
   security definer
   language plpgsql
as $$
begin
   insert into customer_search(customer_id, contact_method, is_stale, term, contact_id)
   values(new.customer_id, 'Email', true, new.email_address, new.id);
   return new;
end;

liquidgenius

-- psql variables can only be used outside functions 
-- plpgsql variables can only be used inside functions
-- session variables can be used both outside and inside functions, bridging the gap between the two types of variables
\set func_name vars
\echo '[':func_name'.sql]'

CREATE OR REPLACE FUNCTION :func_name (arg_execute TEXT)
RETURNS VOID AS $$ DECLARE
BEGIN
END;

liquidgenius

-- Check for table existence
create or replace function table_exists( tbl_name text )
returns boolean as $$
begin
  return exists (
    select 1 from pg_catalog.pg_tables where tablename = tbl_name
  );
end;
$$ language plpgsql;

liquidgenius

CREATE OR REPLACE FUNCTION add_timestamps_to_table(_table text, _type text) RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS created_at %s', _table, _type);
  EXECUTE format('ALTER TABLE %I ADD COLUMN IF NOT EXISTS updated_at %s', _table, _type);
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION set_timestamps_not_null_on_table(_table text) RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE %I ALTER COLUMN created_at SET NOT NULL', _table);

liquidgenius

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

    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

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

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

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

/**
 * 传入轨迹抽样点，获取整条轨迹穿越地区
 * 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