Thread

1. Stored procedures returning rowsets

   Jaroslaw Nozderko <jaroslaw.nozderko@polkomtel.com.pl> — 2002-08-09T13:05:26Z

   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
   
   
   

2. Re: Stored procedures returning rowsets

   Neil Conway <nconway@klamath.dyndns.org> — 2002-08-09T14:42:33Z

   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
   
   
   

3. Re: Stored procedures returning rowsets

   Joe Conway <mail@joeconway.com> — 2002-08-09T15:41:24Z

   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