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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
