Thread: stored procedure revisited

Hi,

I've gone through the mailing list archives and the documentation to
search on this topic, and amazingly there is almost no signs of stored
procedure discussions anywhere.  Once a while a person would ask the
question of how to do SP in postgresql, and it will be greeted with
silence.  Not that anyone is obligated to reply, but the low volume
about this topic seems to mean:

1. this is a very dumb question, rtfm first.

2. no one knows.

When reading through the documentation, it seems there isn't a way to do
SP like in other databases.  The closest thing is the CREATE FUNCTION
command, which can only return one value or a set of one row (of course,
when it's an opaque function it acts just like other SP).  This is very
far from the multi-resultset, multi-level transaction SP in other
databases.  When I go through the ToDo list, SP is not on it either.

I think I am missing something very obvious here.  Can someone please
kind enough explain to me, is there SP for postgresql and where I can
find more information about it?  If there isn't, is there any plan for
implementation?  Seems there needs to be an extra table that would hold
the parsed query tree and a rewrite of the parser to make SP work.

Regards,

yin-so chen

At 09:33 +0200 on 10/10/1999, Yin-So Chen wrote:


> I think I am missing something very obvious here.  Can someone please
> kind enough explain to me, is there SP for postgresql and where I can
> find more information about it?  If there isn't, is there any plan for
> implementation?  Seems there needs to be an extra table that would hold
> the parsed query tree and a rewrite of the parser to make SP work.

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

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.

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

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

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

Hi,

I would like to update about 50000 records from a table of 1000000.  The ids
of records that I _don't_ want to update are in a similar table (containing
some 950000 records).

What is the best way to construct my SQL statement?

TIA

timj