Re: [GENERAL] stored procedure revisited

Herouth Maoz wrote:
>
> AFAIK, there are no stored procedures in PostgreSQL. Maybe they are in a
> low priority, or the developers are just trying to avoid the problems of
> doing them. I'm not a developer, but I get the impression that sets of rows
> are not well-abstracted in PostgreSQL. I think this is also the reason why
> there are no subselects in target lists yet (correct me if I'm wrong).
>

Thanks for replying on my message, Herouth.  Can any developer verify
Herouth's point, please?

> Anyway, sometimes the proper solution for things you stated (i.e. return a
> set of rows without using the entire query every time) are more correctly
> done with views. Other things (procedural things that don't result in sets
> of rows) are handled by functions. So you have a rather small niche for
> which only stored procedures are the most proper tool, and which is not
> covered in PostgreSQL.
>

I beg to differ on the point that SP is a small niche.  Views are in my
mind, a work-around, and a very limited one at that.  What it does is
that instead of typing out something like "select f1.id, f2.name from
foo1 f1, foo2 f2 where f1.id = f2.id", you just type "select * from
view1".  Though it's better than the whole query, we are still sending a
complete query over to the server, and there isn't any flow-control, and
certainly there isn't ways to do both select query and insert query all
at the same time.  SP offers procedural abstraction at the database
layer that cannot be achieved with views or functions.  Functions are
the closest thing to SP in PG, however, it's limited that it can only
return one row, and it requires a syntax of 'select func1(args)' which
is non-intuitive as a substitute for non-resultset SP.  I am not even
sure if functions achieve what SP is supposed to achieve - saving the
server time from reparsing the queries; I have a feeling that functions
are also just place-holders at this point.

> If you think this niche is important, maybe you should convince the rest of
> us here (I never needed to use a stored procedure so far, and I don't
> remember many people using them five years ago when I was in an Oracle
> environment). Or you could prioritize it with money...
>

How robust was SP in Oracle five years ago though?  It's certainly a
feature that has grown up now...  I've been doing exactly the same as
the rest of you - using the aforementioned work-arounds.  If I don't
think SP is important, I certainly have other things to do than to
bother you with something you feel that has work-arounds.  I would love
to hear from the rest of you on why SP isn't important, so I get a
chance to convince you :)  Flames are welcome too :)  If you don't agree
with my POV that these work-arounds aren't the best way to handle the
situation or that we shouldn't be satisfied with work-arounds, please
let me know as well.

Regards,

yin-so chen