Re: How to create a stored procedure in PostgreSQL

Hi Craig,

Thanks a lot for your detailed response.

Regards,
Siva.

-----Original Message-----
From: Craig Ringer [mailto:ringerc(at)ringerc(dot)id(dot)au]
Sent: Friday, August 12, 2011 2:14 PM
To: Siva Palanisamy
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] How to create a stored procedure in PostgreSQL

On 12/08/2011 4:22 PM, Siva Palanisamy wrote:
> Hi All,
>
> I have worked in MS SQL Server where we can create a stored procedure
> that performs some set of queries in tandem. I wish to see a similar
> feature in PostgreSQL. Please guide me.
>
> I searched and found only functions as replacement to stored procedure
> in PostgreSQL! Is that so? How functions & stored procedure can be same
> in any context?

PostgreSQL does not support true stored procedures. They're not the same
thing as functions at all, but most Pg users use functions in place of
stored procedures where functions are good enough to do the job.

PL/PgSQL functions are fairly capable, so they can handle a lot of the
job, including fairly significant data conversion and migration tasks.
However, they are not capable of autonomous transactions so you can't
commit or open a new separate transaction within a function. They're
controlled by the surrounding transaction and can abort that transaction
but not commit it or create a new transaction. There are workarounds
involving using dblink to open a new connection, but they're not lovely.

It's a long-standing limitation and I haven't seen any recent movement
on stored procedure support. Most people use functions where practical,
and where that's not enough they use dblink or use clients outside the
database.

--
Craig Ringe

::DISCLAIMER::
-----------------------------------------------------------------------------------------------------------------------

The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only.
It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in
this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates.
Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of
this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have
received this email in error please delete it and notify the sender immediately. Before opening any mail and
attachments please check them for viruses and defect.

-----------------------------------------------------------------------------------------------------------------------