Oliver

AWS: Python Kinesis Streams

This entry is part 2 of 3 in the series AWS & Python

If you haven’t already done so please refer to the AWS setup section which is part of this series. As time goes on I will continually update this section.

To put something on the Kinesis Stream you need to utilise the “connection_kinesis” you setup already in the previous tutorial on setting up the connection. You will need to set the partition key, data and stream.

response = connection_kinesis.put_record(StreamName=##KINESIS_STREAM##, Data=##DATA##, PartitionKey=##FILE_NAME##)

Depending on your data you may need to utf8 encode. For example below.

bytearray(##MY_DATA##, 'utf8')

To read from the kinesis stream you need to setup the shard iterator then retrieve the data from the stream. Not forgetting to grab the new shard iterator from the returned records. Remember to not query against the queue to fast.

#shardId-000000000000 is the format of the stream
shard_it = connection_kinesis.get_shard_iterator(StreamName=##KINESIS_STREAM##, ShardId='shardId-000000000000', ShardIteratorType='LATEST')["ShardIterator"]
 
recs = connection_kinesis.get_records(ShardIterator=shard_it, Limit=1)
 
#This is the new shard iterator returned after queueing the data from the stream.
shard_it = out["NextShardIterator"]

AWS: Python Setup

This entry is part 1 of 3 in the series AWS & Python

When you want to work with S3 or a Kinesis Stream we first need to setup the connection. At the time of this writing I am using boto3 version 1.3.1.

Next we need to import the package.

import boto3

Next we setup the session and specify what profile we will be using.

profile = boto3.session.Session(profile_name='prod')

The profile name comes from the “credentials” file. You can set the environment variable “AWS_SHARED_CREDENTIALS_FILE” to specify what credentials file to use. You can setup the credentials file like below. You can change the “local” to anything you want. I normally use “stage”, “dev” or “prod”.

[local]
aws_access_key_id=##KEY_ID##
aws_secret_access_key=##SECRET_ACCESS_KEY##
region=##REGION##

Next we need to setup the connection to S3. To do this we will need to use the profile we created above.

connection_s3 = profile.resource('s3')

If we want to also use a Kinesis stream then we need to setup the connection. To do this we will need the profile we created above.

connection_kinesis = profile.client('kinesis')

Python: Enums

There are a variety of different ways to create enums. I show you a few different ways. They are not full version but you get the idea. If you have another way please feel free to add.

Option 1:

def enum(**enums):
    return type('Enum', (), enums)
 
my_enum = enum(NONE=0, SOMEVAL=1, SOMEOTHERVAL=2)

Option 2:
You will notice that you pass in “Enum” into the class. Also this way you can also declare classmethods for getting enum from string or tostring. It’s really your choice how you get the string representation you could either use str(MyEnum.VALUE) or MyEnum.tostring()

from enum import Enum
 
class MyEnum(Enum):
    VALUE = 0
 
    def __str__(self):
        if self.value == MyEnum.VALUE:
            return 'Value'
        else:
            return 'Unknown ({})'.format(self.value)
    def __eq__(self,y):
        return self.value==y
 
    @classmethod
    def fromstring(cls, value):
        """
        Converts string to enum
        """
 
        return getattr(cls, value.upper(), None)
 
    @classmethod
    def tostring(cls, val):
        """
        Converts enum to string
        """
 
        for k, v in vars(cls).iteritems():
            if v == val:
                return k

Option 3:

class MyEnum():
    NONE = 1
    VALUE = 2
    
    def __init__(self, Type):
        if Type is None:
            self.value = MyEnum.VALUE
        else:
            self.value = int(Type)
    def __str__(self):
        if self.value == MyEnum.VALUE:
            return 'Value'
        else:
            return 'None'
    def __eq__(self,y):
        return self.value==y

Python: For Loops

There are a variety of different ways to write a for loop in python. See below for different options. If you have any other suggestions please feel to add a comment.

Loop through a list one record at a time.

for rec in data:
	#Do Something

Loop through a range of numbers up to 100.

for i in range(100)
	#Do Something

Enumerate a list getting the index and value.

for idx, value in enumerate(data):
	#Do Something

Inline for loop.

d = [ x for x in data]

Inline for loop with an if condition.

d = [ x for x in data if x==1]

Java: Connect to Postgres

Below are the steps to setup a connection to a Postgres DB and some other options that you can use.

Pom.xml:

<dependency>
	<groupId>org.postgresql</groupId>
	<artifactId>postgresql</artifactId>
	<version>9.4.1208.jre7</version>
</dependency>

Import:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

Build The Connection:

Class.forName("org.postgresql.Driver");
Connection connection = DriverManager.getConnection(##URL##, ##USER##, ##PASS##);

Preparing the Query:
We utilise “PreparedStatement” to setup the connection. Which will allow us to use parameters in the query.

PreparedStatement ps = connection.prepareStatement("select id from table where column=?")

If your query had parameters (IE: ?) then you will need to pass in the value for each parameter. If you notice there is ##POSITION## and ##VALUE##. Position is the location of where the parameter appears in the query. You can set various types of data for example integer, json, etc.

ps.setString(##POSITION##, ##VALUE##);

After we perform a query we need to retrieve the data. We use “ResultSet” for that. Depending on how we return the data depends on how we get the data after the query succeeds. Below are examples of one line returned vs multiple rows returned.

ResultSet rs = ps.executeQuery();
 
if (rs.next()) {
	int variable = rs.getInt("id");
}
 
while (rs.next()) {
	//Do Something
}

Insert:
If you want to perform an insert you don’t need to do “executeQuery” you can call just “execute”.

ps = connection.prepareStatement("insert into mytable (column) values (?)");
ps.setInt(##POSITION##, ##VALUE##);
ps.execute();

Batching:
Sometimes we have a lot of updates or inserts to perform. We should batch that.

//You setup the preparedStatement and then add to the batch.
ps.addBatch();
 
//You can set a max batch size to send the batch once it hits that amount
if (++count % batchSize == 0) {
	ps.executeBatch();
}

Passing Json as a Parameter:
We create the postgres object. Set the type to json and the value as a string in json format. Next we set the preparedStatement parameter as the postgres object that we just created.

final PGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(value.toString());
 
//Now set the json object into the preparedStatement
ps.setObject(##POSITION##, jsonObject);

Various Commands

Below are some useful commands for those either just learning Ubuntu 14.04 or a resource for various commands. I will continually expand on them.

Directory Exists:

if [ ! -d "##DIRECTORY##" ]; then
fi

Format a Number as two digits even if it is 1:

$(printf %02d $variable)

Increment a Number:

variable=`expr $variable + 1`

Create a new file:

touch ##FILENAME##

Check if a file contains a string:

This will return 0 if it is not found.

$(echo `grep -c '##TextToSearch##' ##FileNameToSearch##`)

Create a link to a file:

ln -s ##OriginalFile## ##LinkedFile##

List all packages installed:

dpkg -l

Remove installed package:

sudo apt-get --purge remove ##PACKAGENAME##

Install Package:

sudo apt-get install ##PACKAGENAME##

Package Exists:

This will return 0 if it is not found.

$(echo `dpkg-query -l | grep -c ##PACKAGE_NAME##`)

Python Package Exists:

This will return 0 if it is not found.

$(echo `pip freeze | grep -c ##PACKAGE_NAME##`)

Get IP Address:

ip addr show

Find a file:

find / -type f -name "##FILENAME##"

Restart Service:

/etc/init.d/postgresql restart
invoke-rc.d postgresql restart

Kill a process:

kill ##PID##

Terminal Loop:

In the terminal let’s say you want to loop and display a value. You can do it like below. I am just printing free memory. But really you can do anything.

while true; do free -m | grep /+ | gawk '{ print $4 }'; sleep 2; done

Switch to root:

sudo su root

Add Text to File:

echo "##TextToAdd##" >> ##FileNameToAddTo##

Add Text Above Other Text:

sed -i 's/^##LookForText##/##TextToAdd##\n&/' ##FileNameToAddTo##

Loop:

until [ $Variable -gt 3 ]
do
done

Postgres: Restore

Restoring a postgres instance is rather straight forward. If you have a better way please feel free to let me know.

pg_restore -c --if-exists -Fd -h ##SERVERNAME## -p ##SERVERPORT## -U ##USER## -d ##DB_NAME## -v ##FILENAME_W_PATH## > ##OUTPUT_DIR## 2>&1

-Fc: Instructs how the data was compressed for restoring.
-Fd: Instructs that the data was compressed by directory for restoring.
–if-exists: Used with -c

Postgres: Backup

Backing up a postgres instance is rather straight forward. You can automate the backup or manual backup. I have documented either or below. If you have a better way please feel free to let me know.

Manual:

pg_dump -h ##SERVERNAME## -p ##SERVERPORT## -U ##USER## -j 8 -c --if-exists -Fd -b -v -f ##FILENAME_W_PATH## ##DB_NAME## > ##OUTPUT_LOG## 2>&1

There are a variety of options you can choose from but the above is a standard I like to use.

-Fc: This compresses the data and allows for restoring via pg_restore
-Fd: This compresses the data to a directory structure for restoring via pg_restore
-j 8: This runs 8 concurrent jobs.
-N mySchema: This excludes the schema mySchema from being backed up.
–if-exists: Used with -c

Automated:

You can also use PgAgent to do scheduled backups and I recommend this however I don’t have the steps yet. That will hopefully come shortly.

You should create a backup user for the automated version. Here is what the user should have.

CREATE USER ##BACKUP_USER## WITH ENCRYPTED PASSWORD '##PASSWORD##';
GRANT CONNECT ON DATABASE ##DATABASE NAME## TO ##BACKUP_USER##;
GRANT USAGE ON SCHEMA public TO ##BACKUP_USER##;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO ##BACKUP_USER##;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ##BACKUP_USER##;

Whichever OS you are using there is a different way to set it up.

Windows:

You need to create “pgpass.conf” file in “%APPDATA%\postgresql” directory.
pgpass should contain connection information such as “hostname:port:database:username:password”
You can then create a batch file to run pg_dump. Example of such a batch file below. You could also write a PS script which is the preferred direction to go.

 @echo off
   for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
     set day=%%i
     set month=%%j
     set year=%%k
   )
 
   for /f "tokens=1-4 delims=: " %%i in ("%time%") do (
     set hour=%%i
     set minute=%%j
     set second=%%k
   )
 
   set backupDir=\\NETWORK_SHARE\%month%_%day%_%year%
   set backupFile=DBName_%month%_%day%_%year%_%hour%_%minute%_%second%.backup
 
   if not exist %backupDir% (mkdir %backupDir%)
 
   C:\"Program Files"\PostgreSQL\9.4\bin\pg_dump.exe -h ##SERVERNAME## -p ##SERVERPORT## -U ##BACKUP_USER## -j 8 -c --if-exists -Fd -b -v -f %backupDir%\%backupFile% ##DB_NAME## > ##OUTPUT_LOG## 2>&1

Ubuntu:

You need to create the .pgpass file in the users home directory. It should contain information such as hostname:port:database:username:password. You will also need to modify the permissions to be owner read/write.

touch .pgpass
chmod 600 .pgpass

Next we need to set the crontab job. By entering “crontab -e”. You can call the backup.sh file on whatever schedule you want.

#! /bin/sh
currentDate=$(date +"%m_%d_%Y")
currentDateTime=$currentDate$(date +"_%H_%M_%S")
 
if [ ! -d ##NETWORK_SHARE##/##DB_NAME## ]
then
        mkdir ##NETWORK_SHARE##/##DB_NAME##
fi
 
if [ ! -d ##NETWORK_SHARE##/##DB_NAME##/$currentDate ]
then
        echo $currentDate
        mkdir ##NETWORK_SHARE##/##DB_NAME##/$currentDate
fi
 
pg_dump -h ##SERVERNAME## -p ##SERVERPORT## -U ##BACKUP_USER## -j 8 -c --if-exists -Fd -b -v -f ##NETWORK_SHARE##/##DB_NAME##/$currentDate/$currentDateTime.backup ##DB_NAME## > ##OUTPUT_LOG## 2>&1

HighCharts: Basic Graphing

This entry is part 1 of 2 in the series React: Highcharts

HighCharts is a pretty cool graphing package. Below is an example of how you can create an universal React class for a HighCharts graph.

You will need to install the package. At the time of this writing I am using 5.0.6.

You will also need to import HighCharts and require the charts.

import Highcharts from "highcharts/highcharts.js";
window.Highcharts = Highcharts;
require("highcharts/highcharts-more.js")(Highcharts);

In the state I hold these values to manage how the chart loads and displays data.

getInitialState: function() {
	return {
		chartSettings: null, //Holds the chart settings data
		loaded: false,	//Determines if the chart has been loaded
		chart: null,	//The chart
		data: [],	//The data to utilize for the chart. It's most likely in series format
	};
},

In the component methods check to see when the class has been loaded with data or reset if needed.

componentDidUpdate: function() {
	if (!this.state.loaded) { //The chart hasn't been loaded with data so load it and refresh the chart
		this.setState({
			loaded: true,
			data: this.props.data
		}, () => { this.chart(); });
	}
},
componentWillReceiveProps: function(newprops) {
	if (this.state.loaded && this.props != newprops) { //The chart has been loaded but the data has changed. Refresh the chart after
		this.setState({
			data: newprops.data
		}, () => { this.chart(); });
	}
},

The class the render method is how the chart assigns to the UI.

render: function() {
	return (<div id={this.props.id}></div>
); },

You can create a “chart” method. Which you can use to manage the display of the chart. The main section of it is how to display the chart after you have modified the chart settings. You could also utilize a props for controlling whether to show the loading or not. Totally up to you.

this.setState({
	loaded: true,			//The data and chart has been loaded
	chart: new Highcharts.Chart(chartSettings) //Set the chart
}, () => {
	if (!this.props.data.length == 0) { //If no data is present yet then show a loading image
		this.state.chart.showLoading();
		this.state.chart.redraw();
	} else {			//The data has been loaded.
		this.state.chart.hideLoading();
		this.state.chart.redraw();
	}
});

In the “chart” method you should clean up your existing chart before generating a new one.

if (this.state.chart !== null) {
	this.state.chart.destroy();
	this.state.chart = null;
}

There are so many ways of controlling the chartsettings. I will try to cover a vast majority of the options. The basic definition looks like this.

chartSettings = $.extend(true, {},
	this.props.chartSettings,
	{
		chart: {
			renderTo: this.props.id,	//The id you passed into the class
			backgroundColor: "",
            		type: this.props.chart_type,	//By passing in the chart type it will be open to various types of charts.
            		height: 500,            	//You can specify the height of the graph if you want.
            		zoomType: "xy",            	//If you want to be able to zoom.	
            	},
            	credits: {
			enabled: false	//Turns off the powered by
		},
            	title: {
                	text: this.props.title,
                	style: { color: "white" }
            	},
            	subtitle: {
                	text: this.props.sub_title
            	},
		tooltip: {
		},
		plotOptions: {
		},
		series: thisInstance.state.data
	});

Tooltip has various options. One I like to use is the “formatter” functionality. This will allow you to modify what is displayed on hover.

tooltip: {
	formatter: function(){
		var pointIndex = this.point.index;
		var seriesName = this.point.series.name;
	}
}

There is also xAxis option. You can do a variety of different things. Depending on how you create your graph determines what options you should use. The type in xAxis can have a few different options. I show you “datetime” below. But you can also choose “linear” which is numerical data as well as “category” which allows you to put string data on the X axis.

xAxis: {
	type: "datetime",
	categories: this.props.categories,
	title: {
		enabled: true,
	},
	showLastLabel: true,
	showFirstLabel: true,
	tickInterval: 15,		//I chose to increment to x value by 15 days. But you can choose whatever you want
	labels: {
		formatter: function () {
			if (the type is a date == "date") {
				return Highcharts.dateFormat("%m/%d", this.value);	//You can format however you like
			} else {
				return this.value;
			}
		}
	}
},

There is also yAxis option. You can do a variety of different things. Depending on how you create your graph determines what options you should use. Here is an example.

yAxis: {
	allowDecimals: true,
	title: {
		align: "high"
	},
	labels: {
		overflow: "justify",
		formatter: function() {
			return this.value;
		}
	},
},

You can add onClick events to series points if you want.

plotOptions: {
	series: {
		point: {
			events: {
				click: function(e){
				}
			}
		}
	}
}

There are various graph types. For example “pie”, “bar”, “scatter”, etc. Here are a few different examples of a basic setup.

plotOptions: {
	pie: {
		allowPointSelect: true,		//When you click the pie slice it moves out slightly
		cursor: "pointer",
		shadow: false,
		dataLabels: {
			enabled: true,
			formatter:function(){
			},
			color: "white",
			style: {
				textShadow: false 
			}
		}
	},
	bar: {
		dataLabels: {
			enabled: true,
			allowOverlap: true,	//Labels will overlap. Turns this off if you don't want your labels to overlap.
		}
	},
	scatter: {
		dataLabels: {
			crop: false,		//Labels will not be hidden
		},
		marker: {
			radius: 3,
			states: {
				hover: {
					enabled: true
				}
			}
		},
		states: {
			hover: {
				marker: {
					enabled: false
				}
			}
		}
	}
}

Highcharts: Add Custom Buttons

This entry is part 2 of 2 in the series React: Highcharts

If you’ve never used HighCharts for your graphing needs I highly suggest it. Very customizable and easy to use.

You will need to require the exporting requirements.

import Highcharts from "highcharts/highcharts.js";
window.Highcharts = Highcharts;
require("highcharts/modules/exporting")(Highcharts);

If you would like to add a custom button to your graph you can use the exporting section like below.

exporting: {
	buttons: {
		customButton: {
			text: "Button Text",
			onclick: function () {
			}
		},
	},
}

React: Export Module

Sometimes we need exportable modules for use through our applications. It is pretty straight forward to export a module.

NPM Installs:

npm install create-react-class --save
npm install prop-types --save
npm install react --save
npm install react-dom --save

Export:

 module.exports = {exportedModuleName:ModuleName};

exportedModuleName is the name that you use in other pages.
ModuleName is the name of the module to export.

The module will look something like this.
This one is just a TD module. But really you can do anything you want.

window.jQuery = window.$ = require("jquery"); 
import React from "react"; 
import ReactDOM from "react-dom";
var createReactClass = require('create-react-class');
var propTypes = require('prop-types');
 
var MyExportableModule = createReactClass({
      render: function() {
            return React.createElement("anyelement", {className: this.props.className, key: this.props.name}, this.props.fields);
      }
});
 
MyExportableModule.PropTypes = {
      name: React.PropTypes.string.isRequired,
      fields: React.PropTypes.array.isRequired,
      className: React.PropTypes.string
};

Distinct Records in Object Array

Sometimes you need to determine the distinct objects in an array or distinct values in array. There are so many ways to do this. One way which I have used at times can be a bit slow depending on the size of your array.
From my investigation there is a lodash version that is much better. Once I do some testing I will update this but for now here is an example.
I expanded on the idea from Stack Exchange.

 var distinct = function(objectArray, param){
      var distinctResult = [];
 
      $.each(objectArray, function(i, currentObject){
            if (param !== null) {
                  if (distinctResult.filter(function(v) { return v[param] == currentObject[param]; }).length == 0)
                  {
                        distinctResult.push(currentObject);
                  }
            } else {
                  if(!exists(distinctResult, currentObject))
            {
                  distinctResult.push(currentObject);
            }
            }
      });
 
      return distinctResult;
};
 
var exists = function(arr, object){
    var compareToJson = JSON.stringify(object);
    var result = false;
    $.each(arr, function(i, existingObject){
        if(JSON.stringify(existingObject) === compareToJson) {
            result = true;
            return false; // break
        }
    });
 
    return result;
};

C#: Connect to WebService

If you need to connect to a webservice method from your C# application you can do as an example like the following below. Notice I also use Newtonsoft for handling the json return. But you can do it however you want depending on your needs. This is just one way of doing it.

 using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Net.Http;
 
string webserviceURL = "http://whatevermyurlis.com/";
string data = String.Format("my_id={0}", myId);
string url = String.Format("{0}MyMethodName?{1}", webserviceURL, data);
System.Net.Http.HttpClient client = new System.Net.Http.HttpClient();
client.BaseAddress = new System.Uri(url);
client.DefaultRequestHeaders.Accept.Add(new System.Net.Http.Headers.MediaTypeWithQualityHeaderValue("application/json"));
 
System.Net.Http.HttpContent content = new StringContent("", UTF8Encoding.UTF8, "application/json");
HttpResponseMessage messge = client.PostAsync(url, content).Result;
if (messge.IsSuccessStatusCode)
{
      string result = messge.Content.ReadAsStringAsync().Result;
      JObject jObject = JObject.Parse(result);
}

Java: ExecutorService / Future

If you want to spin off a bunch of threads and manage them and their responses effectively you can do it this way.

final ExecutorService executor = Executors.newFixedThreadPool(numThreads);
final Collection<Future<JsonNode>> futures = new LinkedList<Future<JsonNode>>();
 
//Write a Loop if you want
final Callable<TYPE> callable = new MyClass();
futures.add(executor.submit(callable));
executor.shutdown();
 
// We need to monitor the queries for their data being returned.
for (final Future<?> future : futures) {
	try {
		final TYPE val = (TYPE) future.get();
	} catch (final InterruptedException e) {
	} catch (final ExecutionException e) {
	}
}

The callable works with a class so you will need that.

package mypackage;
 
import java.util.concurrent.Callable;
import org.apache.log4j.Logger;
 
public class MyClass implements Callable<JsonNode> {
 
    static final Logger logger = Logger.getLogger(MyClass.class);
 
    MyClass() {
    }
 
    /**
     * This is how each caller queries for the data. It can be called many times and runs on threads from the calling class
     * So data is returned as it gets it.
     */
    @Override
    public TYPE call() throws Exception {
        try {
                  return null;
        } catch (final Exception e) {
            return null;
        }
    }
}

React: Page Layout

There are many aspects of React below is just a sample of how you could setup a ReactJs page. Look up what each section does.

Go here to review the React Life Cycle. It is important to review this and understand it so that you dont make mistakes during your development.

NPM Installs:

npm install create-react-class --save
npm install react --save
npm install react-dom --save

Class Setup:

window.jQuery = window.$ = require("jquery");
import React from "react";
import ReactDOM from "react-dom";
import "../css/newpage.css";
var createReactClass = require('create-react-class');
 
var NewPage = createReactClass ({
      getData: function() {
            var params = {};
            
            $.ajax({
                  url: "/my_web/service_method/",
                  dataType: "json",
                  data: params,
                  success: function(data) {
                        this.setState({
                              "data": data
                        }, () => { 
                              //If you want to do something after you get the data loaded
                        });
                  }.bind(this),
                  error: function(xhr, status, err) {
                        console.err("Bad");
                  }.bind(this)
            });
      },
      getInitialState: function() {
            return{
                  "data": [],
            };
      },
    componentDidMount: function() {
    },
    componentWillMount: function() {
          this.getData();
    },
      render: function() {
 
            return (
                  <div key="div">
                  </div>
            );
      }
});
 
ReactDOM.render(<NewPage/>, document.getElementById("app-container"));

Postgres: Functions/Triggers

I usually use pgsql to write my functions.

Example Update Function:
This function could be used as a save/creation of records. It passes back a boolean and id.

 CREATE OR REPLACE FUNCTION Public.fn_my_function(param1 integer, param2 integer, OUT Success Boolean, OUT ID integer)
AS $body$
DECLARE
      any_variable integer;
BEGIN
      $3 = True;
      $4 = 3234;
    
 
      EXCEPTION 
            $3 = False;
            $4 = -1;
            WHEN OTHERS THEN 
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
END
$body$ LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = Public;

Get Function:
You can also return a table.
IE: RETURNS TABLE(id integer, data json) AS $body$

 CREATE OR REPLACE FUNCTION Public.fn_get_function()
RETURNS SETOF Public.My_Table AS $body$
BEGIN
      RETURN QUERY 
      SELECT * 
      FROM public.my_table t
      ORDER BY t.Name;
END
$body$ LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = Public;

Comment Function:

 COMMENT ON FUNCTION Public.fn_my_function(integer, integer) IS '';

Drop Function:
Notice how I use “IF EXISTS”. This is best because if it didn’t exist your script would fail.

 DROP FUNCTION IF EXISTS Public.fn_my_function(Integer, Integer, Integer);

Trigger:

 CREATE OR REPLACE FUNCTION Public.fn_my_function() RETURNS TRIGGER AS $BODY$
DECLARE
    v_old_data json;
    v_new_data json;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
      v_old_data := row_to_json(OLD.*);
      v_new_data := row_to_json(NEW.*);
      RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
      v_old_data := row_to_json(OLD.*);
      RETURN OLD;
    ELSIF (TG_OP = 'INSERT') THEN
      v_new_data := row_to_json(NEW.*);
      RETURN NEW;
    ELSE
      RAISE WARNING '[Public.fn_my_function] - Other action occurred: %, at %',TG_OP,now();
      RETURN NULL;
    END IF;
       
    EXCEPTION
      WHEN data_exception THEN
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
      WHEN unique_violation THEN
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
      WHEN OTHERS THEN
            RAISE WARNING '[Public.fn_my_function] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
            RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = Public;

Postgres: Views

To create a view is rather simple.

 CREATE OR REPLACE VIEW Audit.vw_Your_Table AS
      SELECT *
      FROM Your_table;

Comment on View:

 COMMENT ON VIEW Audit.vw_Your_Table IS 'Display the data';

Postgres: Check Data Type

When you deal with json data type there is the option of using json_typeof(value). However this doesn’t always give you what you expect. For example a date field renders as text. To get around this I have the following two functions. Feel free to use them or suggest alternatives.

Test Value is Date:

 create or replace function is_date(s varchar) returns boolean as $$
begin
  perform s::date;
  return true;
exception when others then
  return false;
end;
$$ language plpgsql;

Test Json Value for Type:

 CREATE OR REPLACE FUNCTION get_json_type(s json) returns text as $$
DECLARE val_type text;
BEGIN
     IF (SELECT public.is_date(s::text)) THEN
        val_type := 'date';
     ELSEIF (json_typeof(s) = 'number') THEN
        val_type := 'double precision';
     ELSEIF (json_typeof(s) = 'string') THEN
        val_type := 'text';
     ELSEIF (json_typeof(s) = 'boolean') THEN
        val_type := 'boolean::integer';
     ELSE
        val_type := json_typeof(s);
     END IF;
 
     return val_type;
END;
$$ language plpgsql;

Postgres: Misc

Here are some misc things you can do in postgres 9.4.

Print to Console:

 raise notice 'VALUE: %', value;

Json Build Object:

 json_build_object('name', value, 'name2': value2)

Json Array:

 json_agg(data)

Extract Month:
You can also extract year, day, etc.

 extract(month from date_column)

Json Querying:
You can extract a field from a json field. You can do:

 rec->>'id'
rec#>'{field, sub_field,value}'
rec->'object'

Row To Json:

 SELECT row_to_json(t)
FROM mytable t

Update With FROM:

 UPDATE mytable l
SET old_val_id=sub.new_val_id
FROM (
     SELECT l2.id, s.id as new_val_id
     FROM mytable l2
          INNER JOIN mysupertable s ON s.id=l2.old_val_id
) sub
WHERE sub.id=l.id;

Inline Script:

 DO $do$
DECLARE id integer;
BEGIN      
 
END $do$ LANGUAGE plpgsql;

If:

 IF CONDITION THEN
END IF;

Let’s say you have a json field and in that field you have a field which tells you what key you should select for certain data. It could be customizable from the user entering data. To dynamically select that field you can do the below.

jsonField->((to_json((jsonField->'key'->'sub_key'))#>>'{}')::text)

Upsert:
Sometimes we want to perform update if the record exists or an insert if it doesn’t. Most of the time we write a function that deals with this on a record by record basis. But what if we want to do a batch update or insert. What do we do then. We perform an upsert. See below. We write the update and return the result in the remainder of the query we check if upsert gave us anything. If it didn’t we perform the insert.

WITH upsert AS (UPDATE MyTable mt
            SET column_name = sub.new_value
            FROM (SELECT mot.id, mot.new_value 
                 FROM MyOtherTable mot
                 ) sub
            WHERE sub.id=mt.related_id 
RETURNING *) 
INSERT INTO MyTable (id, column_name, related_id) 
SELECT ?, ?, ? 
WHERE NOT EXISTS (SELECT * FROM upsert)

Regex Substring:
There are different ways of using regex. This is one way.

substring(column_name from '([0-9]{4}-[0-9]{2}-[0-9]{2})')

PGSQL Loop:
This is one way to loop using pgsql.

DO $do$
DECLARE rec RECORD;
BEGIN
	FOR rec IN SELECT * FROM MyTable
	LOOP
		--We can then use rec like rec.colum_name
	END LOOP;
END $do$ LANGUAGE plpgsql;

Milliseconds to timestamp:

This will return 2017-08-17 21:26:04

select to_timestamp(1503005165);

Postgres: Tables

Below are some common functions for doing table creation and maintenance.

Table Creation:

 CREATE TABLE Public.mytable (
      id BigSerial PRIMARY KEY,
      text_column varchar NOT NULL,
      int_column Integer NOT NULL,
      date_column timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Create Schema:

 CREATE SCHEMA IF NOT EXISTS test;

Create Schema with Authorization:

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION myUser;

Drop Schema Cascade:

DROP SCHEMA IF EXISTS test CASCADE;

Comment On Table:

 COMMENT ON TABLE Public.mytable IS 'A List of data.';

Vacuum:
vacuum has options best to review them.

 vacuum (analyze,verbose);

Drop Constraint:

 ALTER TABLE mytable DROP CONSTRAINT mytable_id_pkey;

Add Constraint:

 ALTER TABLE public.mytable ADD CONSTRAINT mytable_id_pkey PRIMARY KEY (id);

Rename Constraint:

 ALTER TABLE mytable RENAME CONSTRAINT "mytable_id2_fkey" TO "mytable_id3__fkey";

Rename Table Column:

 ALTER TABLE mytable RENAME COLUMN text_column TO text_column2;

Rename Table:

 ALTER TABLE mytable RENAME TO mytable2;

Drop Table:

 DROP TABLE public.mytable;

Add Column to Table:

 ALTER TABLE Public.mytable ADD column_name boolean NOT NULL DEFAULT False;

Alter Column Data Type Json:

ALTER TABLE public.mytable ALTER COLUMN json_col TYPE json USING (json_col::json);

Rename Sequence:

 ALTER SEQUENCE mytable_id_seq RENAME TO mytable_id_seq;

Sequence Table Owner:

 alter sequence mytable_id_seq owned by mytable.id;

Sequence Next Value:

 alter table mytable alter column mytable_id set default nextval('mytable_id_seq');

Add Foreign Key:

 alter table mytable ADD FOREIGN KEY (foreign_id) REFERENCES public.mytable2(foreign_id);

Create Index Json:

 CREATE INDEX mytable_idx ON Public.mytable((Data->'Key'->'Key'->>'value'));

Create Index:

 CREATE INDEX mytable_idx ON public.mytable(id);

Drop Index:

 DROP INDEX public.mytable_idx;

Re-Cluster Table:

 Cluster mytable using mytable_pkey;

Trigger:

 CREATE TRIGGER "tg_mytrigger" BEFORE UPDATE OF my_column OR INSERT ON public.mytable FOR EACH ROW EXECUTE PROCEDURE public.mytablestrigger();