liquidgenius

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

liquidgenius

Table of Contents

• TL;DR
• General
  • Method Information:
  • Benchmarking Information:
  • Postgres/PostGIS Information:
• Create Benchmark Function
• Tests

liquidgenius

Someone asked how to get the latlong from a specific road near a town on OpenStreetMap.

If you need to do it only once (e.g., you're about to go on a trip, and your GPS cannot find your destination city, but allows you to enter GPS coordinates), you can use Nominatim, OpenStreetMap's geocoding interface.

If you need to do it multiple times, in a programmatic manner, there are at least two ways to do that.

Note: I worked with OSM data a couple of years ago, but I don't have an OSM database on my local laptop right now, so some instructions will be a bit fuzzy. I do apologize in advance.

PostGIS queries on a local OSM DB

liquidgenius

CREATE TRIGGER person_notify AFTER INSERT OR UPDATE OR DELETE ON income
FOR EACH ROW EXECUTE PROCEDURE notify_trigger(
  'id',
  'email',
  'username'
);

CREATE TRIGGER income_notify AFTER INSERT OR UPDATE OR DELETE ON income
FOR EACH ROW EXECUTE PROCEDURE notify_trigger(
  'id',

liquidgenius

#!/bin/bash
#
# Install Postgres 9.1, PostGIS and create PostGIS template on a clean Ubuntu 11.10 Oneiric Ocelot box
# http://wildfish.com

# add the ubuntu gis ppa
sudo apt-get -y install python-software-properties
sudo add-apt-repository ppa:ubuntugis/ubuntugis-unstable 
sudo apt-get update 

liquidgenius

--
-- Create a valid GEOMETRY in 4326 from a lat/lng pair
--
-- @param lat A numeric latitude value.
--
-- @param lng A numeric longitude value.
--  
--

CREATE OR REPLACE FUNCTION CDB_LatLng (lat NUMERIC, lng NUMERIC) RETURNS geometry as $$ 

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