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
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
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);
}
10) Create a function in Postgresql to use the 'C' function just we have created.
Test the function by running the following command in PostgreSQL shell.
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 postgres10) 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;
SELECT exec_shell_command('send_sms.sh 9894835581 "Hello Rampro, How are you?"');
OR Any other shell command.
OR Any other shell command.
That's it. This function can be called from any procedure to execute any arbitrary shell command.
