Skip to main content

Posts

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.

http://manojadinesh.blogspot.in/2012/06/fatal-python-error-pyinitialize-unable.html

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))

try:

  smtpObj = smtplib.SMTP(smtp,port)
  smtpObj.starttls()
  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>
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

PG_FUNCTION_INFO_V1(shell_exec);
Datum
shell_exec(PG_FUNCTION_ARGS)
{
    text  *arg1 = PG_GETARG_TEXT_P(0);
    char  *Command=VARDATA(arg1);
    int32 result=system(Command);   
    PG_RETURN_TEXT_P(result);
}

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.so', 'shell_exec'  
LANGUAGE C STRICT;

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 
http://sourceforge.net/ 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…