Add plsh to Postgres 9.3

Nope no BitCoins yet... So, yet again....If this helped you in any way and you have some spare BitCoins, you may donate them to me -16tb2Rgn4uDptrEuR94BkhQAZNgfoMj3ug
PLEASE.....Pretty PLEASE?

Problem with Postgres is that you cannot move, rename files etc from within the Postgres psql. Well this has changed with this brilliant binary from Peter Eisentraut, see https://github.com/petere/plsh
Thanks Peter!!
Peter's Blog
The binary allow you to run a bash or sh shell from a function. See below how I implemented it

Download the GZ file from https://github.com/petere/plsh and place it in /opt/temp, the file is called plsh-1.20130823.tar.gz

super173:/opt/temp # ls -ltr
total 1467348
-rw-r--r-- 1 root root 11182 Jul 15 12:11 plsh-1.20130823.tar.gz
super173:/opt/temp #

Gunzip and untar the file, my Postgres is installed under /opt/app, so the -C will extract the files to a new directory under /opt/app/pgdata, this is up to you where you want to place it

super173:/opt/temp # gunzip plsh-1.20130823.tar.gz
super173:/opt/temp # tar -xvf plsh-1.20130823.tar -C /opt/app/pgdata/
plsh-1.20130823/
plsh-1.20130823/.travis.yml
plsh-1.20130823/COPYING
plsh-1.20130823/Makefile
plsh-1.20130823/NEWS
plsh-1.20130823/README.md
plsh-1.20130823/plsh--1--2.sql
plsh-1.20130823/plsh--unpackaged--1.sql
plsh-1.20130823/plsh-inline.sql
plsh-1.20130823/plsh-noinline.sql
plsh-1.20130823/plsh.c
plsh-1.20130823/plsh.control
plsh-1.20130823/test/
plsh-1.20130823/test/expected/
plsh-1.20130823/test/expected/crlf.out
plsh-1.20130823/test/expected/event_trigger.out
plsh-1.20130823/test/expected/function.out
plsh-1.20130823/test/expected/init.out
plsh-1.20130823/test/expected/init_1.out
plsh-1.20130823/test/expected/inline.out
plsh-1.20130823/test/expected/psql.out
plsh-1.20130823/test/expected/psql_1.out
plsh-1.20130823/test/expected/trigger.out
plsh-1.20130823/test/expected/trigger_1.out
plsh-1.20130823/test/sql/
plsh-1.20130823/test/sql/crlf.sql
plsh-1.20130823/test/sql/event_trigger.sql
plsh-1.20130823/test/sql/function.sql
plsh-1.20130823/test/sql/init.sql
plsh-1.20130823/test/sql/inline.sql
plsh-1.20130823/test/sql/psql.sql
plsh-1.20130823/test/sql/trigger.sql
super173:/opt/temp #

I renamed the plsh-1.20130823 to plsh just to make it easier to remember, the plsh-1.20130823 directory was created with the tar command above. I then changed the ownership of the plsh directory to postgres as well.

super173:/opt/app/pgdata # cd /opt/app/pgdata

super173:/opt/app/pgdata # ls -ltr
total 8
drwx------ 16 postgres postgres 4096 Jun 30 09:16 9.3
drwxrwxr-x  4 postgres postgres 4096 Jul 15 12:16 plsh-1.20130823

super173:/opt/app/pgdata # mv plsh-1.20130823 plsh
super173:/opt/app/pgdata # chown -R postgres:postgres plsh/

su to your postgres user and change directory to /opt/app/pgdata/plsh

super173:/opt/app/pgdata # su - postgres
postgres@super173:~> cd /opt/app/pgdata/plsh/

Now to build the binary, use make, make install

postgres@super173:/opt/app/pgdata/plsh> make
gcc -O2 -Wall -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -I. -I. -I/opt/app/PostgreSQL/9.3/include/postgresql/server -I/opt/app/PostgreSQL/9.3/include/postgresql/internal -D_GNU_SOURCE -I/opt/local/20130819/0d87f820-0a63-11e3-9b6d-000c29d23b02/include/libxml2 -I/usr/local/include/libxml2 -I/usr/local/include -c -o plsh.o plsh.c
gcc -O2 -Wall -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fpic -shared -o plsh.so plsh.o -L/opt/app/PostgreSQL/9.3/lib -L/opt/local/20130819/0d87f820-0a63-11e3-9b6d-000c29d23b02/lib -L/usr/local/lib -Wl,--as-needed -Wl,-rpath,'/opt/app/PostgreSQL/9.3/lib',--enable-new-dtags
cp plsh-inline.sql plsh.sql
cp plsh.sql plsh--2.sql
postgres@super173:/opt/app/pgdata/plsh> make install
/bin/mkdir -p '/opt/app/PostgreSQL/9.3/lib/postgresql'
/bin/mkdir -p '/opt/app/PostgreSQL/9.3/share/postgresql/extension'
/bin/mkdir -p '/opt/app/PostgreSQL/9.3/share/postgresql/extension'
/usr/bin/install -c -m 755 plsh.so '/opt/app/PostgreSQL/9.3/lib/postgresql/plsh.so'
/usr/bin/install -c -m 644 ./plsh.control '/opt/app/PostgreSQL/9.3/share/postgresql/extension/'
/usr/bin/install -c -m 644 ./plsh--unpackaged--1.sql ./plsh--1--2.sql plsh--2.sql '/opt/app/PostgreSQL/9.3/share/postgresql/extension/'
postgres@super173:/opt/app/pgdata/plsh>

If you encounter problem with the make complaining about the pg_config file, you can make the binary with

make PG_CONFIG=/where/ever/your/pgconfig/file/is/pg_config
make install PG_CONFIG=/where/ever/your/pgconfig/file/is/pg_config

You can test your make with make installcheck

postgres@super173:/opt/app/pgdata/plsh> make installcheck
/opt/app/PostgreSQL/9.3/lib/postgresql/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=. --psqldir='/opt/app/PostgreSQL/9.3/bin' --inputdir=test --dbname=contrib_regression init function trigger crlf psql inline event_trigger
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
NOTICE: database "contrib_regression" does not exist, skipping
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test init ... ok
test function ... ok
test trigger ... ok
test crlf ... ok
test psql ... ok
test inline ... ok
test event_trigger ... ok

=====================
 All 7 tests passed.
=====================

postgres@super173:/opt/app/pgdata/plsh>

And you are done, keep in mind if you want to move, rename, copy etc any files, Postgres must have permissions to do this, what I did is to give Postgres sudo rights to the /bin directory where cp, mv, chown etc lives
To do this, as root type visudo and add this to the file

visudo
# Runas alias specification

# User privilege specification
root ALL=(ALL) ALL
postgres ALL=(ALL) NOPASSWD: /bin/

# Uncomment to allow people in group wheel to run all commands
# %wheel ALL=(ALL) ALL

An example of the function creation is below, keep in mind you have to create the extension plsh, touch a test file in /opt/temp called testThisFile

CREATE EXTENSION plsh;

CREATE or REPLACE FUNCTION renameFileBash(inputDir text, inputFile text, outputDir text, outputFile text) RETURNS text AS '
#!/bin/bash
cp $1$2 $3$4;
cp $3$4 $3$4".bkp";
sudo mv $3$4 /opt/temp/$4".tmp";
sudo chown testuser:testgroup /opt/temp/$4".tmp";
sudo chmod 664 /opt/temp/$4".tmp";
sudo mv /opt/temp/$4".tmp" /opt/temp/$4".somextension";
echo $3$4;
' LANGUAGE plsh;

commit;

select renameFileBash('/opt/temp/', 'testThisFile', '/opt/temp/', 'mvFileAllOver')