From: Ezequiel Tolnay <mail@etolnay.com.ar>
To: pgsql-admin@postgresql.org
Subject: functions vs stored procedures
Date: Thu, 07 Jul 2005 20:47:02 +1000
Message-ID: <daj141$25ks$1@news.hub.org> (raw)
Hi guys,
I've been using PostgreSQL for about one year, and implemented two
systems using it, and I'm pretty happy with PG. But coming from MSSQL, I
just can't get used to the annoyance of having to create a type for
every single function that returns a rowset. It is frankly cumbersome.
I found that the only feasible way to make this work is by
maintaining a script that would delete and recreate everything (types
and functions), because you can't modify a stored procedure's result,
nor you can modify a type if it has dependencies, so you have to either
create a new type with the modification, modify the function and then
drop the original type. I really can't find a reason why this has to be
so complicated.
I realise that for a matter of optimisation, typing results from
functions is a very good idea, but I believe that functions should not
replace the functionality of stored procedures. It should only
complement them. Otherwise you are in a similar annoying situation as
you were with MSSQL7, where you had stored procedures but not functions.
Is the lack of stored procedures a feature made on purpose in PG? Is
there any reason why there are ones available but not the others? Just
in case there are PG-only people that don't have experience with stored
procedures, these are mainly tools for two things: running scripts
(functions can supplement SP for this) and return arbitrary result-sets
according (or not) to the parameters passed. The second feature is
mostly used to encapsulate functionality to separate database logic from
an interface like a web application. It also limits access to the data
eficiently reducing the likelihood of penetration or hacking.
Is there a wishlist where I could post this, along with many many
reasons why stored procedures should be made available (i.e. with
untyped results)? Would it be too complicated or discouraged for any
reason? Perhaps there is a way to deal with this issue to simplify its
administration that I'm not aware of?
I also think it would be a great thing to have some sort of
conditional statements and variables outside functions, just as part of
the language. Creating and running scripts on the fly is very useful.
Currently I have a shell structure that creates a function, executes it
and deletes it, so I write all my script in plpgsql inside it, but it
would be so much more convenient to be able to do it without this
trickery. Don't you think?
Thanks for reading through, I hope this gets to the PG developers,
and/or generates some discussions and end-up in good ideas for the next
release.
Cheers!
Ezequiel Tolnay
Good Business Technology Pty Ltd
Sydney - Australia
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: mail@etolnay.com.ar
Subject: Re: functions vs stored procedures
In-Reply-To: <daj141$25ks$1@news.hub.org>
References: <daj141$25ks$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox