Skip to main content


Showing posts from August, 2012

How To Send E-Mail From PostgreSQL

Hi ,

If you want to send E-Mails from PostgreSQL, then use the below Python 3.2 Script as below. I have used ActivePython 3.2 with PostgreSQL 9.1 for sending E-Mails from PostgreSQL.

If you want to configure the Python 3.2 with PostgreSQL 9.1 then, please refer the below steps.

Once, your Python 3.2 successful then follow the below steps to send an e-mail.

Step 1

postgres=# CREATE OR REPLACE FUNCTION public.send_email(_from Text,_password Text,smtp Text,port INT,receiver text, subject text, send_message text) RETURNS TEXT  LANGUAGE plpython3u
AS $function$

import smtplib
sender = _from
receivers = receiver
message = ("From: %s\nTo: %s\nSubject: %s\n\n %s"
 % (_from,receiver,subject,send_message))


  smtpObj = smtplib.SMTP(smtp,port)
  smtpObj.login(_from, _password)
  smtpObj.sendmail(sender, receivers,message)
  print ('Successfully sent email')
except SMTPException…

OS Results From PostgreSQL

Hi ,

As of now, we are using "\i" or using "PL/SH" script for getting the OS command results from PostgreSQL. However, below is the one more option where we can get the required info.

Step 1
#include "postgres.h"
#include "fmgr.h"
#include <string.h>
#include <unistd.h>

    text  *arg1 = PG_GETARG_TEXT_P(0);
    char  *Command=VARDATA(arg1);
    int32 result=system(Command);   

Step 2
-bash-3.2$ more Makefile
PG_CONFIG = /opt/PostgresPlus/9.0AS/bin/pg_config
MODULES = shell_exec
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

Step 3
make and .so file to $PGHOME/lib  or $PGHOME/lib/postgres/

Step 4
postgres=# CREATE OR REPLACE FUNCTION shell_exec(text) RETURNS int                           AS '', 'shell_exec'  

Postgres-XC Setup

Hi ,
Finally i have configured PG-XC in my local box .. Please find the below steps how to configure PG-XC .. 
Steps To Configure PG-XC ======================== Step 1 ====== Download PG-XC Version 1.0 from the below link projects/postgres-xc/files/ Version_1.0/pgxc-v1.0.0.tar.gz/download
Step 2 ====== mkdir -p /opt/Postgres-xc chown -R postgres:postgres /opt/Postgres-xc/ tar -zxvf pgxc-v1.0.0.tar.gz
Step 3 ====== Pre-Requistes ---------------- Readline,Bison,Flex yum -y install readline* yum -y install bison* yum -y install flex*
./configure --prefix=/opt/Postgres-xc/ make make install
Step 4 ====== Setup of GTM  {Global Transaction Manager} ------------------------------ -------------------- -bash-4.1$ mkdir data_gtm -bash-4.1$ chmod 700 data_gtm/ -bash-4.1$ /opt/Postgres-xc/bin/initgtm -Z gtm -D /usr/local/pgsql/data_gtm   
# It will create gtm.conf file under data_gtm location .. Then change the port of gtm server and listen_addresses if required.
Below ar…