Thread: why no stored procedures?
Hey guys, Is there any reason why there are no stored procedures for postgresql or does this have to do with the ability to add your own procedural language? Roy.
roypgsqlgen@xemaps.com wrote:
> Hey guys,
>
> Is there any reason why there are no stored procedures for postgresql or
> does this have to do with the ability to add your own procedural
> language?
What exactly do you mean with "there are no stored
procedures"?
I mean, we have more procedural languages than any other
database and with the upcoming v7.2 we will even have
reference cursors for PL/pgSQL to pass them into and out of
functions. So could you detail your question please?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
Jan Wieck responded in an irritated manner thusly: > What exactly do you mean with "there are no stored > procedures"? i won't pretend to know what what the original poster had in mind when asking his question but i'm a newbie at postgres and i have some confusion as to how a function maps to a stored procedure. the big question in my mind is how i can treat the results from a function as a table. i can't. i posted a message to the pgsql-sql list which i have included below. this tells me that postgres _does not_ support stored procedures in the manner that those of us coming from microsoft sql server and/or oracle. thanks! rjsjr > i'm reading the postgres documentation and i'm specifically > interested in creating stored procedures so that i can keep > as much of the business logic in the database as possible. > while reading 13.1.3 (SQL Functions on Composite Types) in > the Programmer's Guide i come across the phrase... > "When calling a function that returns a row, we cannot > retrieve the entire row. We must either project an > attribute out of the row or pass the entire row into > another function. > SELECT name(new_emp()) AS nobody;" Unfortunately, PostgreSQL functions do not yet return result sets. This is on the "todo" list but unfortunately requires an overhaul of how postgresql functions work. -Josh Berkus > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck > Sent: Tuesday, August 14, 2001 9:51 PM > To: roypgsqlgen@xemaps.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] why no stored procedures? > > > roypgsqlgen@xemaps.com wrote: > > Hey guys, > > > > Is there any reason why there are no stored procedures > for postgresql or > > does this have to do with the ability to add your own procedural > > language? > > What exactly do you mean with "there are no stored > procedures"? > > I mean, we have more procedural languages than any other > database and with the upcoming v7.2 we will even have > reference cursors for PL/pgSQL to pass them into and out of > functions. So could you detail your question please? > > > Jan > > -- > > #=========================================================== > ===========# > # It's easier to get forgiveness for being wrong than for > being right. # > # Let's break this rule - forgive me. > # > #================================================== > JanWieck@Yahoo.com # > > > > _________________________________________________________ > Do You Yahoo!? > Get your free @yahoo.com address at http://mail.yahoo.com > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
> -----Original Message----- > From: Jan Wieck [mailto:JanWieck@Yahoo.com] > Sent: Tuesday, August 14, 2001 10:51 PM > To: roypgsqlgen@xemaps.com > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] why no stored procedures? > > roypgsqlgen@xemaps.com wrote: > > Hey guys, > > > > Is there any reason why there are no stored procedures for > postgresql or > > does this have to do with the ability to add your own procedural > > language? > > What exactly do you mean with "there are no stored > procedures"? > > I mean, we have more procedural languages than any other > database and with the upcoming v7.2 we will even have > reference cursors for PL/pgSQL to pass them into and out of > functions. So could you detail your question please?Jan, In the world of Oracle, SQL Server and Sybase, a stored procedure accepts and returns multiple parameters. In SQL Server and Sybase, you can also return result sets very easily. Oracle also has a way for stored procedures to return result sets though in my opinion, it's a rather clumsy implementation. Stored procedures on those databases get compiled the first time they run and are stored compiled for use again later. Interbase does this too. From what I understand, postgresql does not have any of this available to it. It has procedural languages available to it, but not 'stored procedures'. Functions are fine, but only being able to return one parameter is going to hurt performance since I will have to run more select statements from the client side to get any other info that my function might have changed. Plus, from what I read, functions aren't compiled ahead of time either. At this point its a toss up for my company between interbase and postgresql (or GOOD GOD MSDE!). I read somewhere that interbase is missing a lot of features that postgresql has, but the fact that it has stored procedures is really attractive to my company. I would like to go with postgresql, but I've been trying to figure out how I would do what I currently do in stored procedures limited to functions and select statements. We also have many nested transactions so that might hurt as well. Can someone please point me to a page that compares the features of interbase to postgresql? I heard interbase is missing key functions like CASE and other such things. But I really can't find any documentation on that sort of thing. Greatly appreciated, Roy.
roypgsqlgen@xemaps.com writes: > From what I understand, postgresql does not have any of this available to > it. It has procedural languages available to it, but not 'stored > procedures'. Functions are fine, but only being able to return one > parameter is going to hurt performance since I will have to run more select > statements from the client side to get any other info that my function might > have changed. Plus, from what I read, functions aren't compiled ahead of > time either. The "functions returning resultsets" problem is definitely being looked at. I'm not sure what the status is. Also, whether functions are compiled and cached depends on the procedural language in question. PL/pgSQL definitely does this (caches a parse tree of the function). I don't think PL/TCL does, but I'm not sure. PL/pgSQL also caches query plans automatically, and PL/TCL has support for doing it explicitly. -Doug -- Free Dmitry Sklyarov! http://www.freesklyarov.org/ We will return to our regularly scheduled signature shortly.
> roypgsqlgen@xemaps.com writes: > > > From what I understand, postgresql does not have any of this available to > > it. It has procedural languages available to it, but not 'stored > > procedures'. Functions are fine, but only being able to return one > > parameter is going to hurt performance since I will have to run more select > > statements from the client side to get any other info that my function might > > have changed. Plus, from what I read, functions aren't compiled ahead of > > time either. > > The "functions returning resultsets" problem is definitely being > looked at. I'm not sure what the status is. > > Also, whether functions are compiled and cached depends on the > procedural language in question. PL/pgSQL definitely does this > (caches a parse tree of the function). I don't think PL/TCL does, > but I'm not sure. PL/pgSQL also caches query plans automatically, and > PL/TCL has support for doing it explicitly. > PostgreSQL also supports compiled C functions. This feature has significant performance advantages over run-of-the-mill stored procedures. -- Joe
Creating a C function to replace each stored proc is hardly a good solution. Or is it? Unfortunately my C skills are rusty at best. I'm glad to hear that the postgresql developers are attempting to add the ability to return result sets. Does anyone have any idea how that is coming along? Also are they planning on creating stored procs at all to return more than one parameter? Roy. > -----Original Message----- > From: Joe Conway [mailto:joseph.conway@home.com] > Sent: Wednesday, August 15, 2001 12:19 PM > To: roypgsqlgen@xemaps.com; Doug McNaught > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] why no stored procedures? > > PostgreSQL also supports compiled C functions. This feature > has significant > performance advantages over run-of-the-mill stored procedures. > > -- Joe > >