Hi Craig, Thanks a lot for your detailed response. Regards, Siva. -----Original Message----- From: Craig Ringer [mailto:ringerc@xxxxxxxxxxxxx] Sent: Friday, August 12, 2011 2:14 PM To: Siva Palanisamy Cc: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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. ----------------------------------------------------------------------------------------------------------------------- -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
