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

2008/10/14 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Mon, Oct 13, 2008 at 3:56 PM, Vladimir Dzhuvinov <vd(at)valan(dot)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.
>

I like this functionality - but simply I am wating and searching
sponsoring. It's about 2 months of work.

>> 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.

with some bad impacts - creating and dropping every temp table means
system tables modifications. Intensivelly using of temp tables needs
intensive vacuum of system tables and hash significant negative
impacts.

>
>> 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 started on MSSQL server, then MySQL is maybe better for you.
Lot of knowleages should be same. PostgreSQL is much more near Oracle
or DB2, that multirecordset (if I have good knowleadges) do via
cursors.

> 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.
>

It's true - PostgreSQL doesn't support some important features about
transactions - explicit controling of transactions, autonomous
transactions, ... I hope so this functionality will be implemented in
some days.

Regards
Pavel Stehule

> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>