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
