Re: PL/pgSQL stored procedure returning multiple result sets (SELECTs)? - Mailing list pgsql-general

Hi guys,


Ugh, why is it so hard to let go of this topic ;)


I want to tell you why I find stored procedures useful and summarise my
understanding on how they differ from functions. I hope this user
perspective would be helpful to a future Postgres implementation.


So what is my use of stored procedures?

I work on a system for internal payments between the employees of a
company. The design called for a clear separation of clients and server,
where responsibilities should be clear cut and client software should
know as little as possible about the data model on the SQL server and
its relational implementation. It's like going out with a beautiful
woman - you just want to enjoy her fair qualities and don't really want
to know how she's constructed ;)

A server API was constructed consisting of about two dozen stored
procedures. The stored procedures basically encapsulated a set of
INSERTs, UPDATEs and some control flow operators to perform specific
tasks. So, if a user sits in front of his PC and decides to check his
account balance and then make a payment to her colleague, the client
software connects on her behalf to the DB and issues the following SP calls:

   CALL login('username', 'secret password'); -- returns session token

   CALL get_account_balance('session token');

   CALL make_payment('session token', 'to account-ID-12345');

   CALL logout('session token');


The client software makes only stored procedure calls to the DB; notice
there is no direct access to tables, no BEGIN/COMMIT/ROLLBACK, etc. -
all this is handled internally by the stored procedures. To enforce this
protocol client connections were granted EXECUTE only; table SELECTs,
UPDATEs and DELETEs are not allowed. If a stored procedure needs to
return data to the client, this is done through a simple SELECT to the
client (using OUT parameters would complicate interfacing).

So, from a software engineering point of view, stored procedures were
very good to have.


But how do they relate to *functions*?

Initially I wasn't quite sure why stored procedures should differ from
functions, but after some thought it became clear:


1. First and foremost, they are meant to serve different purposes:

A function is... hmm, a function, a mapping: given a set of arguments it
returns a single and well defined value: f(x,y) -> z

The purpose of stored procedures, on the other hand, is to encapsulate
an (arbitrary) bunch of SQL commands, a mini-program of sort.

The other differences they have seem to be secondary, stemming from
their purposes.

2. (leads from 1) Functions are stackable, stored procedures are "nestable":

    ADDTIME(NOW(), SEC_TO_TIME(3600));

        vs.

    CREATE PROCEDURE my_task()
        BEGIN
        ...
        CALL some_other_task(param1, @param2);
        ...
        END


3. (also leads from 1) Functions must have a defined return type, stored
procedures normally have no such requirement.

4. Functions have restriction on table access, they are only allowed to
work on their IN arguments (MySQL). Stored procedures have virtually no
 limitations - they can execute arbitrary SQL - access tables, do
transactions and pass data directly to the client using SELECTs.


I personally find the ability to do a direct SELECT from a stored
procedure to the client extremely useful (MySQL 5+). It makes data
retrieval easier to program than having a stored procedure return open
cursors or OUT parameters (saving additional SELECT queries after the
CALL() ).


Ok, enough work for today, I'm getting a beer now :)


--
Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C