PostgreSQL 'C' Function to execute shell command

How to run a shell command from a PostgreSQL function?

A typical example is to send sms through 'gnokii' with AT drivers.

1) Install gnokii with the following command
sudo apt-get install gnokii

2) Gnokii config shall be in /etc/gnokiirc with appropriate contents. I have used the following for Nokia 6681 connected to server by USB cable. Most of the mobiles will work with this configuration.

[global]

port = /dev/ttyACM0

model = AT

connection = serial

3) For compiling the PostgreSQL 'C' function, install the server development package by issuing the command

sudo apt-get install postgresql-server-dev-8.4

4) Create a shell script to Send SMS through gnokii, make it executable and place it in the common PATH preferably in /usr/local/bin/
  Contents of send_sms.sh:
#!/bin/bash

echo $2 | /usr/bin/gnokii --config /etc/gnokiirc --sendsms $1

if [ $? -eq 0 ]  # Test exit status of "gnokii" command.

then

  exit 0

else  

  exit -1

fi

5) Create a PostgreSQL C function to execute any shell command:
   Contents of exec_shell_command.c

/* POSTGRESQL C Function to execute shell command */
#include <string.h>
#include "postgres.h"
#include "fmgr.h"

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

/* by value */

PG_FUNCTION_INFO_V1(exec_shell_command);

Datum
exec_shell_command(PG_FUNCTION_ARGS)
{
    text *p1 = PG_GETARG_TEXT_P(0); 
    char *command = VARDATA(p1);
    int retVal = system(command);
    PG_RETURN_INT32(retVal);
}

6) Compile the above c file to generate object code.

gcc -c -fpic -I/usr/include/postgresql/8.4/server exec_shell_command.c -o exec_shell_command.o

The above code is a single line command.

7) Generate shared library from the object code generated in step 6.

gcc -shared -o exec_shell_command.so exec_shell_command.o 

The above code is a single line command.

8) Copy the shared object to PostgreSQL library path.

sudo cp -f exec_shell_command.so /usr/lib/postgresql/8.4/lib/

The above code is a single line command.

9) Add the user 'postgres' to the group 'dialout' to get permission to access USB serialport

sudo usermod  --groups dialout --append postgres

10) Create a function in Postgresql to use the 'C' function just we have created.

CREATE OR REPLACE FUNCTION exec_shell_command(text)

  RETURNS integer AS

'$libdir/exec_shell_command.so', 'exec_shell_command'

  LANGUAGE c STRICT;

Test the function by running the following command in PostgreSQL shell.

SELECT exec_shell_command('send_sms.sh 9894835581 "Hello Rampro, How are you?"');

OR Any other shell command.

That's it.  This function can be called from any procedure to execute any arbitrary shell command.