pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
functions vs stored procedures
5+ messages / 3 participants
[nested] [flat]

* functions vs stored procedures
@ 2005-07-07 10:47  Ezequiel Tolnay <mail@etolnay.com.ar>
  0 siblings, 1 reply; 5+ messages in thread

From: Ezequiel Tolnay @ 2005-07-07 10:47 UTC (permalink / raw)
  To: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: functions vs stored procedures
@ 2005-07-14 13:17  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Ezequiel Tolnay <mail@etolnay.com.ar>
  0 siblings, 1 reply; 5+ messages in thread

From: Tom Lane @ 2005-07-14 13:17 UTC (permalink / raw)
  To: Ezequiel Tolnay <mail@etolnay.com.ar>; +Cc: pgsql-admin

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: functions vs stored procedures
@ 2005-07-15 00:57  Ezequiel Tolnay <mail@etolnay.com.ar>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 5+ messages in thread

From: Ezequiel Tolnay @ 2005-07-15 00:57 UTC (permalink / raw)
  To: pgsql-admin

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
> 



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: functions vs stored procedures
@ 2005-07-15 13:16  Alvaro Herrera <alvherre@alvh.no-ip.org>
  parent: Ezequiel Tolnay <mail@etolnay.com.ar>
  0 siblings, 1 reply; 5+ messages in thread

From: Alvaro Herrera @ 2005-07-15 13:16 UTC (permalink / raw)
  To: Ezequiel Tolnay <mail@etolnay.com.ar>; +Cc: pgsql-admin

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)



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: functions vs stored procedures
@ 2005-07-18 07:23  Ezequiel Tolnay <mail@etolnay.com.ar>
  parent: Alvaro Herrera <alvherre@alvh.no-ip.org>
  0 siblings, 0 replies; 5+ messages in thread

From: Ezequiel Tolnay @ 2005-07-18 07:23 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@alvh.no-ip.org>

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2005-07-18 07:23 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-07-07 10:47 functions vs stored procedures Ezequiel Tolnay <mail@etolnay.com.ar>
2005-07-14 13:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-07-15 00:57   ` Ezequiel Tolnay <mail@etolnay.com.ar>
2005-07-15 13:16     ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2005-07-18 07:23       ` Ezequiel Tolnay <mail@etolnay.com.ar>

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