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