Thread: Stored procedures returning rowsets
Hi, I've searched list archives, but the problem I'm interested in seems not to be frequently mentioned. I've read in 7.3 development doc about table functions. However, are there any plans to implement procedures/functions returning arbitrary rowsets (not only from existing table) that may be handled without those restrictions, as well as return code, as it is, for example, in Sybase ASE ? Regards, Jarek Jaroslaw Nozderko GSM +48 601131870 / Kapsch (22) 6075013 jaroslaw.nozderko@polkomtel.com.pl IT/CCBS/RS - Analyst Programmer
Jarosław Nozderko <jaroslaw.nozderko@polkomtel.com.pl> writes: > I've searched list archives, but the > problem I'm interested in seems not to > be frequently mentioned. I've read in > 7.3 development doc about table functions. > However, are there any plans to implement > procedures/functions returning arbitrary > rowsets (not only from existing table) Yes -- read the proposals on -hackers and -patches sent in by Joe Conway by more information. As of recent CVS, you can call a table function and specify an "anonymous composite type", which basically tells the function (at runtime) what kind of data it should return. Cheers, Neil -- Neil Conway <neilconway@rogers.com> PGP Key ID: DB3C29FC
Jarosław Nozderko wrote:
> Hi,
>
> I've searched list archives, but the
> problem I'm interested in seems not to
> be frequently mentioned. I've read in
> 7.3 development doc about table functions.
> However, are there any plans to implement
> procedures/functions returning arbitrary
> rowsets (not only from existing table)
> that may be handled without those restrictions,
> as well as return code, as it is, for
> example, in Sybase ASE ?
>
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
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?
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
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
On Mon, Aug 12, 2002 at 10:55:55AM +0200, Jaros?aw Nozderko wrote: > > 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. Actually MS bought their code base from Sybase AFAIK, so yes, MSSQL is modelled after Sybase. :-) Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
Previously >Perhaps not all these factors are always important, but in big and >heavy loaded systems it's really unimaginable to send "raw" queries. Interesting, I have heard this often... mainly from SQL Server folk.... I find it hard to understand what is bad about "raw" sql.... here is why : As I understand it, every query goes through the stages of parse,execute, [and possibly fetch(es)]. Stored procedures can only save you the parse phase (at best) - which is usually a tiny component of the total cost/elapsed time (and when it is not, then you probably have a data warehouse with an ad-hoc query tool issueing complex queries - which is not ameniable to the use of procedures either). Furthur several databases managers save (or provide methods to) the parse tree for a query class (Oracle and DB2 for instance), so in these cases there is zero difference in performance between "raw" and "procedured" sql. I dont want to do a "bash SQL Server" thing here (as its quite a good product)...but I do wonder if it has a very expensive parse phase. regards Mark
> As I understand it, every query goes through the stages of > parse,execute, [and possibly fetch(es)]. > Stored procedures can only save you the parse phase (at best) - which is > usually a tiny component of the total cost/elapsed time (and when it is > not, then you probably have a data warehouse with an ad-hoc query tool I'm not so sure about this, parsing can be quite expensive for small result sets.
Hi Mark, > > Previously > > >Perhaps not all these factors are always important, but in big and > >heavy loaded systems it's really unimaginable to send "raw" queries. > > Interesting, > > I have heard this often... mainly from SQL Server folk.... > I find it hard to understand what is bad about "raw" sql.... > here is why : > > As I understand it, every query goes through the stages of > parse,execute, [and possibly fetch(es)]. I think there is also plan/optimize stage between parse and execute... And it may be expensive. Another advantage of stored procedures is possibility of encapsulating business logic on server side only. Regards, Jarek Jaroslaw Nozderko GSM +48 601131870 / Kapsch (22) 6075013 jaroslaw.nozderko@polkomtel.com.pl IT/CCBS/RS - Analyst Programmer
Jaroslaw Nozderko sez: } Hi Mark, } } > } > Previously } > } > >Perhaps not all these factors are always important, but in big and } > >heavy loaded systems it's really unimaginable to send "raw" queries. } > } > Interesting, } > } > I have heard this often... mainly from SQL Server folk.... } > I find it hard to understand what is bad about "raw" sql.... } > here is why : } > } > As I understand it, every query goes through the stages of } > parse,execute, [and possibly fetch(es)]. } } I think there is also plan/optimize stage between parse and execute... } And it may be expensive. Another advantage of stored procedures } is possibility of encapsulating business logic on server side only. Excapsulating business logic on the DB server seems to be the best reason I've heard. In fact, I am in the process of writing a large web application and, more and more, I find that I want to write server-side plpgsql functions to encapsulate transactions, simplifying the programmatic interaction with the database to SELECT Func(args, ...) for the most part, particularly for updates. It is not, however, clear to me the difference between a stored procedure which can be CALL'd and a function which must be SELECT'd. Can anyone explain why the distinction is important? } Regards, } Jarek --Greg
Gregory Seidman <gss+pg@cs.brown.edu> writes:
> It is not, however, clear to me the difference between a stored procedure
> which can be CALL'd and a function which must be SELECT'd. Can anyone
> explain why the distinction is important?
As of 7.3 we should have reasonable support for functions returning
rowsets. The main thing that a CALL-type interface could offer is the
ability for a single procedure call to return *multiple* rowsets.
You can sort of fake this now by returning multiple cursors, but it's
awkward.
The real problem with anything like this is figuring out how to
declare/bind/access output parameters from a CALL. That doesn't
fit into SQL very well, since SQL has no such thing as a global
variable ...
regards, tom lane
Gregory Seidman wrote: > Excapsulating business logic on the DB server seems to be the best reason > I've heard. In fact, I am in the process of writing a large web application > and, more and more, I find that I want to write server-side plpgsql > functions to encapsulate transactions, simplifying the programmatic > interaction with the database to SELECT Func(args, ...) for the most part, > particularly for updates. > > It is not, however, clear to me the difference between a stored procedure > which can be CALL'd and a function which must be SELECT'd. Can anyone > explain why the distinction is important? A SELECT'd function needs a predetermined row structure so the planner can deal with column names of the output, joins to other row sources (i.e. tables/views/table functions), and WHERE clause criteria. So you must know what is to be returned (column names and types) at least in time to specify it in the SELECT statement (ala the new anonymous composite type just committed) Note this capability is new in the upcoming 7.3. A CALL'd stored procedure is stand-alone. It isn't combined with any other row sources, you can't get just specific columns by name, and you can't apply a WHERE clause. However, because of all these restrictions, you also don't need to know the return row structure in advance. A CALL'd stored procedure can produce a different structure given different inputs, or even multiple resultsets with different structures on one call. This can be useful (at least) while debugging your stored proc. There are some front end report writers targeted at MS SQL Server which can deal with these ambiguities. This capability has been discussed, but is not planned for 7.3. And there is no agreement that it will ever be implemented -- so if you're interested, make your voice heard ;-). Joe
Jaroslaw Nozderko wrote: >Hi Mark, > > >I think there is also plan/optimize stage between parse and execute... >And it may be expensive. Another advantage of stored procedures >is possibility of encapsulating business logic on server side only. > >Regards, >Jarek > >Jaroslaw Nozderko >GSM +48 601131870 / Kapsch (22) 6075013 >jaroslaw.nozderko@polkomtel.com.pl >IT/CCBS/RS - Analyst Programmer > Hi Jarek, Yes, I should have mentioned where plan and optimize fit in, I thinking about your very point with the comment referring to the "saving of the parse tree" - I kind of assumed that the plan information was implied here (but ..er .. yes didnt point it out explicitly)... Plan and optimize are typically only expensive for complex queries... and high speed on-line business applications are usually designed use as simple queries as possible - (optimization issues are part of this reason). It is quite interesting to examine the TPC A|B|C benchmark full disclosure reports - there is clearly considerable effort put into keeping the data access simple. Your other point - business logic in the server....is very interesting - I have been thinking about this recently: here goes - <rant> 1 Vendor Lock In Every database verdor does server languages differently, so you get vendor lock in of your business rules - which inhibits your ability to switch backends. (like when you get terrible service from your database vender). 2 Business Logic In The Application Server I remember when folk started putting business logic in the database - primarily to avoid network calls in client server applications, and to provide some reusability and limited encapsulation of logic. These days application servers are the rule, and they are typically "close" to your database server, so network is not an issue. Re-usability and encapsulation and usually *much better* supported within application servers than within database server languages. </rant> So I am thinking that the primary reasons for the creation of extensive database server based languages are no longer applicable today. However, I am not suggesting the removal of database server languages, but I think their importance in the scheme of things has diminished. best wishes Mark