Re: Stored procedures returning rowsets - Mailing list pgsql-general

Hi Joe and Neil,

> I see Neil answered your question, but I'll add to it a bit.
> If you're
> looking for something like the way Sybase stored procedures work (I
> haven't used Sybase but I presume it is similar to MS SQL
> Server), you

I've heard that MS SQL Server was modeled after Sybase, but I'm
not sure if this is true.

> won't see it in 7.3, at least. In other words, do you want to do:
>
>     exec sp_my_proc_name
>
> and have it return un arbitrarily formed result set?
>

It would be nice.

> We have had some discussions regarding that, but decided in favor of
> table functions because they are much more useful in many
> ways. You can
> join them with other tables, and apply selection criteria to their
> output. And the anonymous composite type feature recently added will
> improve the flexibility of the table function approach greatly. Also
> there is a patch waiting to be accepted which will allow the
> creation of
> named composite types which are not tables or views.
>
> But, I do agree that sometimes the MSSQL/Sybase approach is
> very useful.
> Maybe for 7.4 if enough people can be convinced. There have
> been recent
> discussions regarding implementing "CREATE PROCEDURE" and "CALL
> my_procedure" which are steps in this direction.
>
> Joe
>

In my opinion, it's perfectly normal and very usful to retrieve
arbitrary data from database. Stored procedures are really helpful
here, for the following reasons:
- code is stored on the server side, compiled, optimized, etc.
  It's much more efficient than planning and optimizing each incoming
  query,
- query is a business logic - if it's located in each client,
  it's harder to maintain the whole system,
- even the calls to procedures/functions are usually much shorter
  than underlying queries - it may decrease network traffic.

Perhaps not all these factors are always important, but in big and
heavy loaded systems it's really unimaginable to send "raw" queries.
I work with billing system of the cell phone operator and that's
definitely a good example of such situation.
I think this is the major drawback (there are not many of them :))
of Postgres comparing to commercial databases. I know about
several cases where Postgres was considered to be used and was
rejected just for this reason.
Anyway, it's great product :)

Regards,
Jarek

Jaroslaw Nozderko
GSM +48 601131870 / Kapsch (22) 6075013
jaroslaw.nozderko@polkomtel.com.pl
IT/CCBS/RS - Analyst Programmer