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

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