Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

On Mon, Oct 13, 2008 at 3:56 PM, Vladimir Dzhuvinov <vd@valan.net> wrote:
> Hi Merlin,
>
>> Stored procedure support is a pretty complicated feature.  They differ
>> with functions in two major areas:
>>
>> *) input/output syntax.  this is what you are dealing with
>> *) manual transaction management.  stored procedures should allow you
>> emit 'BEGIN/COMMIT' and do things like vacuum.
>>
>> IIRC, I don't think there was a consensus on the second point or if it
>> was ok to implement the syntax issues without worrying about
>> transactions.
>
> I understand the situation, that a range of facets such as syntax, SP
> i/o and the overall fit of SPs into the architecture of PG should be
> considered. What do the Postgres gurus say about stored procedures?

Not too much, there hasn't been a huge emphasis on getting them
because we already have functions which are extremely powerful.

> My SQL experience is rather limited, but I've got the impression that
> every RDBMS has got its own philosophy about matters relational and I
> expect Posgresql to be no different. So probably an improvised hack
> wouldn't be of much use here and things should be thought over.

Using temp tables inside a function isn't hacky.  It was just awkward
in older versions of postgresql because of limitations of the
postgresql engine.

> Anyway, at this point I'm finished with my evaluation of Postgresql. The
> MySQL solution which I've got now works reasonably well. It's just that
> at this moment my investment into MySQL is still relatively small and I
> wanted to check my options before I dig myself too deeply into MySQL to
> make a potential sensible migration too expensive :)

If you are the type of programmer that likes to use the database as an
engine to make your application development easier, you will
eventually regret your decision.

merlin