Skip to main content

pgBucket beta2 is ready

Hi Everyone,

I would like to inform to you all that, pgBucket beta2[Simple concurrent job scheduler for postgresql] version is ready with more stability.

Thank you all in advance for your inputs/comments/suggestions.

--Dinesh

Comments

Popular posts from this blog

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…

:: Rownum in postgresql ::

Hi All,

As we know,we can generate rownum with window fuctions also(Rank).In oracle we have "rownum" pseduo column,but we don't in postgresql.
Here is the one of the solution for generating rownum in postgresql.

In Oracle
-----------

SQL> CREATE SEQUENCE ROWNUMSEQ;

Sequence created.


SQL> SELECT EMPNO,ROWNUMSEQ.NEXTVAL AS "ROWNUM" FROM EMP;

EMPNO ROWNUM
---------- ----------
7369 1
7499 2
7521 3
7566 4
7654 5
7698 6
7782 7
7788 8
7839 9
7844 10
7876 11
7900 12
7902 13
7934 14


In PostgreSQL
----------------

postgres=# CREATE SEQUENCE ROWNUMSEQ;
CREATE SEQUENCE

postgres=# CREATE TABLE TEST(T INT);
CREATE TABLE

postgres=# INSERT INTO TEST VALUES(GENERATE_SERIES(1,14));
INSERT 0 14


postgres=# SELECT T,NEXTVAL('ROWNUMSEQ') AS ROWNUM FROM TES…

DBLINK in PostgreSQL

Hi,

Find the below DBLINK concepts in PostgreSQL, PostgresPlus as well.


PostgreSQL
----------
Foreign Data Wrapper
--------------------
This is the one utility which encapsulates the data from the different Database servers using Handler functions.

Ex:- In PG 9.1, we can encapsulates the data from My Sql and store to PostgreSQL using handlers.
CREATE FOREIGN DATA WRAPPER HANDLER VALIDATOR ;

Currently(<= 9.0) PostgreSQL compatible with only remote PostgreSQL. From 9.1 onwards, we can make the PostgreSQL to communicate with SQL Server/My SQL through these wrappers. Servers ------- This is the one object which is having the Data wrapper methodology as well the remote host connectivity details. Ex:- CREATE SERVER FOREIGN DATA WRAPPER OPTIONS (address , port ,dbname );

User Mapping
------------
This is the one object which maps the current dbserver user/role to remote dbserver user.

Ex:-CREATE USER MAPPING FOR SERVER OPTIONS (username , password );

DBLINK
------
Dblink is one o…