Thread: functions vs stored procedures

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

Ezequiel Tolnay <mail@etolnay.com.ar> writes:
> 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.

Yup.  See coming attractions at, eg,
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES

            regards, tom lane

Thanks for the tip, but unfortunately id didn't address any of my
concerns. I alreay use the version 8, and I'm aware of the possibility
of using arrays for the results, which allows for some flexibility. But
unfortunately arrays are not records, and I'm limited to values of the
same types, and cannot refer the values from their column names. To
create this function I also have to build the arrays for each result,
casting all the values to text in a way that I would be able to convert
back to the original value.

As it is, the workaround is more complicated than the problem itself, so
I think a script droping and recreating all the types and the functions
that use them is preferred.

¿Does anyone know if there is a reason why PostgreSQL lacks stored
procedures? i.e. the type that is executed with the EXECUTE command
(e.g. EXECUTE myproc(1, 2, 3)) and capable of returning an arbitrary
number of rowsets of arbitrary types.

Thanks anyway :o)

Ezequiel Tolnay

Tom Lane wrote:
> Ezequiel Tolnay <mail@etolnay.com.ar> writes:
>
>>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.
>
>
> Yup.  See coming attractions at, eg,
> http://developer.postgresql.org/docs/postgres/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
> http://developer.postgresql.org/docs/postgres/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

On Fri, Jul 15, 2005 at 10:57:04AM +1000, Ezequiel Tolnay wrote:
> Thanks for the tip, but unfortunately id didn't address any of my
> concerns. I alreay use the version 8, and I'm aware of the possibility
> of using arrays for the results, which allows for some flexibility. But
> unfortunately arrays are not records, and I'm limited to values of the
> same types, and cannot refer the values from their column names. To
> create this function I also have to build the arrays for each result,
> casting all the values to text in a way that I would be able to convert
> back to the original value.

Wow, incredible.  You misread the whole documentation.

--
Alvaro Herrera (<alvherre[a]alvh.no-ip.org>)
"We are who we choose to be", sang the goldfinch
when the sun is high (Sandman)

Alvaro Herrera wrote:
> Wow, incredible.  You misread the whole documentation.

I certainly did, I read it with more care this time. It is a great
solution when we're required to return a single record with a custom
type, which are few but there certainly are.

I wonder if you know any tricks to do something similar but returning
rowsets instead of a single record? I'm currently doing some tests with
cursor references, which I've been sugested on another thread.

Thanks for your help, and sorry for not paying the appropriate attention  to your previous hint ;)

Regards,

Ezequiel Tolnay