pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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



view thread (5+ messages)  latest in thread

Message-ID: <daj141$25ks$1@news.hub.org>
Permalink:  ../daj141$25ks$1@news.hub.org/
Also on:    postgresql.org/message-id/daj141$25ks$1@news.hub.org

 · 

reply

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