Обсуждение: PL/pgSQL stored procedure returning multiple result sets (SELECTs)?

Hi,

I've got a financial MySQL database where the application accesses data
through a layer of stored procedures. For various reasons I'm currently
investigating my options to migrate to another SQL RDBMS.

Postgresql seems to offer a few nice advantages over MySQL (e.g.
stricter data integrity through checks and constraints, etc.) and I got
quite excited about it.

However, after consulting the docs and running a few tests, it looks
like Postgresql misses a crucial feature which my application depends
upon - returning multiple SELECT result sets from functions/stored
procedures.

To illustrate, I've got a number of MySQL stored procedures that look
approximately like this:

CREATE PROCEDURE list_user_accounts(IN user_id INT)

    BEGIN

    -- Return first result set (single row)
    SELECT * FROM users WHERE id = user_id;

    -- Return second result set (zero or more rows)
    SELECT * FROM accounts WHERE account_holder = user_id;

    END;


So, is it true that as of Postgresql 8.3 there is no way to have a
pgpqsql function return multiple SELECTs?


Vladimir Dzhuvinov

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

am  Mon, dem 13.10.2008, um 12:17:21 +0300 mailte Vladimir Dzhuvinov folgendes:
>
> However, after consulting the docs and running a few tests, it looks
> like Postgresql misses a crucial feature which my application depends
> upon - returning multiple SELECT result sets from functions/stored
> procedures.
>
> So, is it true that as of Postgresql 8.3 there is no way to have a
> pgpqsql function return multiple SELECTs?

You can write so called SRF (Set Returning Function), read more about
this here:
http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS

Simple example:

test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$begin a:=1;b:=1;return
next;a:=2;b:=3;return next; end;$$language plpgsql; 
CREATE FUNCTION
test=*# select * from srf();
 a | b
---+---
 1 | 1
 2 | 3
(2 rows)


or, simpler in plain sql:

test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select 1,3;$$language
sql;
CREATE FUNCTION
test=*#
test=*#
test=*# select * from srf();
 a | b
---+---
 1 | 3
(1 row)



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

2008/10/13 Vladimir Dzhuvinov <vd@valan.net>:
> Hi,
>
> I've got a financial MySQL database where the application accesses data
> through a layer of stored procedures. For various reasons I'm currently
> investigating my options to migrate to another SQL RDBMS.
>
> Postgresql seems to offer a few nice advantages over MySQL (e.g.
> stricter data integrity through checks and constraints, etc.) and I got
> quite excited about it.
>
> However, after consulting the docs and running a few tests, it looks
> like Postgresql misses a crucial feature which my application depends
> upon - returning multiple SELECT result sets from functions/stored
> procedures.
>
> To illustrate, I've got a number of MySQL stored procedures that look
> approximately like this:
>
> CREATE PROCEDURE list_user_accounts(IN user_id INT)
>
>        BEGIN
>
>        -- Return first result set (single row)
>        SELECT * FROM users WHERE id = user_id;
>
>        -- Return second result set (zero or more rows)
>        SELECT * FROM accounts WHERE account_holder = user_id;
>
>        END;
>
>
> So, is it true that as of Postgresql 8.3 there is no way to have a
> pgpqsql function return multiple SELECTs?

Hello,

it's true. You can use setof cursors instead.

http://www.postgresql.org/docs/8.1/static/plpgsql-cursors.html

regards
Pavel Stehule
>
>
> Vladimir Dzhuvinov
>
> --
> Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
>
>

>> So, is it true that as of Postgresql 8.3 there is no way to have a
>> pgpqsql function return multiple SELECTs?

> it's true.

Thank you for the definite answer, Pavel :)

I came across a blog post of yours (
http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
) as well as several list posts indicating that multiple result sets
might be in the working. Should I check the situation again when 8.4 is
released?


> You can use setof cursors instead.

Cursors, unfortunately, look cumbersome in this situation and will break
the existing API (all transactions encapsulated within SPs, clients
allowed to do CALL only). Anyway, thanks everyone for the cursors tip :)


Vladimir

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

am  Mon, dem 13.10.2008, um 11:34:03 +0200 mailte A. Kretschmer folgendes:
> or, simpler in plain sql:
>
> test=# create or replace function srf (OUT a int, OUT b int) returns setof record as $$select 1,2;select
1,3;$$languagesql; 
> CREATE FUNCTION
> test=*#
> test=*#
> test=*# select * from srf();
>  a | b
> ---+---
>  1 | 3
> (1 row)

Sorry, i have overlooked that this isn't the expected result and thanks
to Pavel for the rectification.



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

2008/10/13 Vladimir Dzhuvinov <vd@valan.net>:
>
>>> So, is it true that as of Postgresql 8.3 there is no way to have a
>>> pgpqsql function return multiple SELECTs?
>
>> it's true.
>
> Thank you for the definite answer, Pavel :)
>
> I came across a blog post of yours (
> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
> ) as well as several list posts indicating that multiple result sets
> might be in the working. Should I check the situation again when 8.4 is
> released?
>

I have only very raw prototype, so I am sure, so this feature will not
be in 8.4, and I am not sure about 8.5. It's nice feature, but I am
not force to complete and clean code, and I am not able create patch.
If you would do it, I am, with pleasure, send you source code, that
allows multirecord sets.

>
>> You can use setof cursors instead.
>
> Cursors, unfortunately, look cumbersome in this situation and will break
> the existing API (all transactions encapsulated within SPs, clients
> allowed to do CALL only). Anyway, thanks everyone for the cursors tip :)
>
>
> Vladimir
>
> --
> Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
>
>

On Mon, 13 Oct 2008 12:17:21 +0300
Vladimir Dzhuvinov <vd@valan.net> wrote:

> CREATE PROCEDURE list_user_accounts(IN user_id INT)
>
>     BEGIN
>
>     -- Return first result set (single row)
>     SELECT * FROM users WHERE id = user_id;
>
>     -- Return second result set (zero or more rows)
>     SELECT * FROM accounts WHERE account_holder = user_id;
>
>     END;

I'd say returning multiple recordset is useful to save connections
and transferred data.
You can't get the same with a left join (users fields will be
repeated over and over) and you can't get the same with 2 separated
statements since they will need 2 connections.

But from the client side, suppose it PHP... if the first
statement return no record and the second one return 3 records, how
can I know?
What about functions like pg_num_fields?

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

>> I came across a blog post of yours (
>> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
>> ) as well as several list posts indicating that multiple result sets
>> might be in the working. Should I check the situation again when 8.4 is
>> released?

> I have only very raw prototype, so I am sure, so this feature will not
> be in 8.4, and I am not sure about 8.5. It's nice feature, but I am
> not force to complete and clean code, and I am not able create patch.
> If you would do it, I am, with pleasure, send you source code, that
> allows multirecord sets.

Yes, I'll be glad to examine your patch. At least to get an idea of
what's involved in implementing multiple result sets.

Please, send the code or a link to it directly to my email (so as not to
spam the list ;)

Greetings from Bulgaria,

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

On Mon, Oct 13, 2008 at 8:09 AM, Vladimir Dzhuvinov <vd@valan.net> wrote:
>>> I came across a blog post of yours (
>>> http://okbob.blogspot.com/2007/11/stacked-recordset-multirecordset.html
>>> ) as well as several list posts indicating that multiple result sets
>>> might be in the working. Should I check the situation again when 8.4 is
>>> released?
>
>> I have only very raw prototype, so I am sure, so this feature will not
>> be in 8.4, and I am not sure about 8.5. It's nice feature, but I am
>> not force to complete and clean code, and I am not able create patch.
>> If you would do it, I am, with pleasure, send you source code, that
>> allows multirecord sets.
>
> Yes, I'll be glad to examine your patch. At least to get an idea of
> what's involved in implementing multiple result sets.

Stored procedure support is a pretty complicated feature.  They differ
with functions in two major areas:

*) input/output syntax.  this is what you are dealing with
*) manual transaction management.  stored procedures should allow you
emit 'BEGIN/COMMIT' and do things like vacuum.

IIRC, I don't think there was a consensus on the second point or if it
was ok to implement the syntax issues without worrying about
transactions.

I'll give you two other strategies for dealing with multiple result
sets in pl/pgsql:
*) temp tables: it's very easy to create/dump/drop temp tables and use
them in later transactions.  previous to 8.3 though, doing it this way
was a pain because of plan invalidation issues.

*) arrays of composites (8.2+)
create table foo(a int, b int, c int);
create table bar(a text, b text, c text);

pl/sql:
create function foobar(foos out foo[], bars out bar[]) returns record as
$$
  select (select array(select foo from foo)),
    (select array(select bar from bar));
$$ language sql;

pl/pgsql:
create function foobar(foos out foo[], bars out bar[]) returns record as
$$
  begin
    foos := array(select foo from foo);
    bars := array(select bar from bar);
    return;
  end;
$$ language plpgsql;

select foos[1].b from foobar();

Customize the above to taste. For example you may want to return the array dims.

By the way, if you are writing client side code in C, you may want to
look at libpqtypes (http://libpqtypes.esilo.com/)...it makes dealing
with arrays and composites on the client sides much easier.  For 8.3
though it requires a patched libpq.

merlin

Hi Merlin,

> Stored procedure support is a pretty complicated feature.  They differ
> with functions in two major areas:
>
> *) input/output syntax.  this is what you are dealing with
> *) manual transaction management.  stored procedures should allow you
> emit 'BEGIN/COMMIT' and do things like vacuum.
>
> IIRC, I don't think there was a consensus on the second point or if it
> was ok to implement the syntax issues without worrying about
> transactions.

I understand the situation, that a range of facets such as syntax, SP
i/o and the overall fit of SPs into the architecture of PG should be
considered. What do the Postgres gurus say about stored procedures?

My SQL experience is rather limited, but I've got the impression that
every RDBMS has got its own philosophy about matters relational and I
expect Posgresql to be no different. So probably an improvised hack
wouldn't be of much use here and things should be thought over.

Anyway, at this point I'm finished with my evaluation of Postgresql. The
MySQL solution which I've got now works reasonably well. It's just that
at this moment my investment into MySQL is still relatively small and I
wanted to check my options before I dig myself too deeply into MySQL to
make a potential sensible migration too expensive :)

Maybe I'm going to revisit Postgresql again in 2009 or 2010 :)

Vladimir

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

> CREATE PROCEDURE list_user_accounts(IN user_id INT)
>
>     BEGIN
>
>     -- Return first result set (single row)
>     SELECT * FROM users WHERE id = user_id;
>
>     -- Return second result set (zero or more rows)
>     SELECT * FROM accounts WHERE account_holder = user_id;
>
>     END;
>
>
> So, is it true that as of Postgresql 8.3 there is no way to have a
> pgpqsql function return multiple SELECTs?
>
>
> Vladimir Dzhuvinov
>
>
Have you considered returning XML instead? You should be able to get
what your looking for much easier with an XMLAGG.

Artacus

On Mon, Oct 13, 2008 at 3:56 PM, Vladimir Dzhuvinov <vd@valan.net> wrote:
> Hi Merlin,
>
>> Stored procedure support is a pretty complicated feature.  They differ
>> with functions in two major areas:
>>
>> *) input/output syntax.  this is what you are dealing with
>> *) manual transaction management.  stored procedures should allow you
>> emit 'BEGIN/COMMIT' and do things like vacuum.
>>
>> IIRC, I don't think there was a consensus on the second point or if it
>> was ok to implement the syntax issues without worrying about
>> transactions.
>
> I understand the situation, that a range of facets such as syntax, SP
> i/o and the overall fit of SPs into the architecture of PG should be
> considered. What do the Postgres gurus say about stored procedures?

Not too much, there hasn't been a huge emphasis on getting them
because we already have functions which are extremely powerful.

> My SQL experience is rather limited, but I've got the impression that
> every RDBMS has got its own philosophy about matters relational and I
> expect Posgresql to be no different. So probably an improvised hack
> wouldn't be of much use here and things should be thought over.

Using temp tables inside a function isn't hacky.  It was just awkward
in older versions of postgresql because of limitations of the
postgresql engine.

> Anyway, at this point I'm finished with my evaluation of Postgresql. The
> MySQL solution which I've got now works reasonably well. It's just that
> at this moment my investment into MySQL is still relatively small and I
> wanted to check my options before I dig myself too deeply into MySQL to
> make a potential sensible migration too expensive :)

If you are the type of programmer that likes to use the database as an
engine to make your application development easier, you will
eventually regret your decision.

merlin

2008/10/14 Merlin Moncure <mmoncure@gmail.com>:
> On Mon, Oct 13, 2008 at 3:56 PM, Vladimir Dzhuvinov <vd@valan.net> wrote:
>> Hi Merlin,
>>
>>> Stored procedure support is a pretty complicated feature.  They differ
>>> with functions in two major areas:
>>>
>>> *) input/output syntax.  this is what you are dealing with
>>> *) manual transaction management.  stored procedures should allow you
>>> emit 'BEGIN/COMMIT' and do things like vacuum.
>>>
>>> IIRC, I don't think there was a consensus on the second point or if it
>>> was ok to implement the syntax issues without worrying about
>>> transactions.
>>
>> I understand the situation, that a range of facets such as syntax, SP
>> i/o and the overall fit of SPs into the architecture of PG should be
>> considered. What do the Postgres gurus say about stored procedures?
>
> Not too much, there hasn't been a huge emphasis on getting them
> because we already have functions which are extremely powerful.
>

I like this functionality - but simply I am wating and searching
sponsoring. It's about 2 months of work.

>> My SQL experience is rather limited, but I've got the impression that
>> every RDBMS has got its own philosophy about matters relational and I
>> expect Posgresql to be no different. So probably an improvised hack
>> wouldn't be of much use here and things should be thought over.
>
> Using temp tables inside a function isn't hacky.  It was just awkward
> in older versions of postgresql because of limitations of the
> postgresql engine.

with some bad impacts - creating and dropping every temp table means
system tables modifications. Intensivelly using of temp tables needs
intensive vacuum of system tables and hash significant negative
impacts.

>
>> Anyway, at this point I'm finished with my evaluation of Postgresql. The
>> MySQL solution which I've got now works reasonably well. It's just that
>> at this moment my investment into MySQL is still relatively small and I
>> wanted to check my options before I dig myself too deeply into MySQL to
>> make a potential sensible migration too expensive :)
>

if you started on MSSQL server, then MySQL is maybe better for you.
Lot of knowleages should be same. PostgreSQL is much more near Oracle
or DB2, that multirecordset (if I have good knowleadges) do via
cursors.

> If you are the type of programmer that likes to use the database as an
> engine to make your application development easier, you will
> eventually regret your decision.
>

It's true - PostgreSQL doesn't support some important  features about
transactions - explicit controling of transactions, autonomous
transactions, ... I hope so this functionality will be implemented in
some days.

Regards
Pavel Stehule

> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/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

On Tue, Oct 14, 2008 at 3:45 PM, Vladimir Dzhuvinov <vd@valan.net> wrote:
>
> 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 ;)

This is a good philosophy, and also it makes me think you will be more
comfortable over here.  PostgreSQL is more popular with the 'in the
database' crowd.  mysql caters more to the 'as little as possible in
the database' crowd.

> 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.

I think your understanding is off here.  Functions can encapsulate
arbitrary collection of statements...as I said previously, there are
two principle differences:
*) functions have implicit created transaction, procedures do not
*) how you pass data to/from the procedure body.  (functions return a
scalar, record, or a set)

Functions are limited in the sense that it is awkward to return
multiple sets, but are much more flexible how they can be integrated
into queries -- you can call a function anywhere a scalar or a set is
allowed -- in addition to the monolithic procedure style.

> 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

you can do this easily via functions.

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

not so, functions can return void.

> 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.

This is completely incorrect. postgresql functions can do anything,
you are describing an 'immutable function' in postgresql parlance.
These are used in special cases like indexable expressions.

> 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() ).

you can do this in postgreql, just only return 1 set...

create function get_foo() returns setof foo as
$$
  select * from foo;
$$ language sql;

While the inability to return directly two sets from the same function
is annoying (I would use arrays today, this was one of the reasons why
we wrote libpqtypes),  you have to understand that in virtually all
other respects postgresql pl/pgsql is light years beyond the lousy psm
implementation in mysql.  The way we handle cursors, iteration, error
handing, optimizable expressions and such has undergone years of
refinement.

Just as a 'for example', look how you can trap errors and do some
recovery inside a pl/pgsql routine:
http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

That feature alone can help you enormously.  Lest you think I'm
biased, I dba a mysql box professionally...every time I pop into the
mysql shell I feel like I'm stepping backwards in time about 5 years.
Don't let the inability to return multiple sets trip you up...you are
missing the big picture.

ok :-) enough advocacy...
merlin

On Tue, 14 Oct 2008 16:51:29 -0400
"Merlin Moncure" <mmoncure@gmail.com> wrote:

> Functions are limited in the sense that it is awkward to return
> multiple sets, but are much more flexible how they can be
> integrated into queries -- you can call a function anywhere a
> scalar or a set is allowed -- in addition to the monolithic
> procedure style.

From a security point of view... stored procedures can't be called
inside another statement making it harder to hide them for sql
injection.

> While the inability to return directly two sets from the same
> function is annoying (I would use arrays today, this was one of
> the reasons why we wrote libpqtypes),  you have to understand that
> in virtually all other respects postgresql pl/pgsql is light years
> beyond the lousy psm implementation in mysql.  The way we handle

That's one of the reasons that made me chose postgresql in spite of
mysql. PostgreSQL is easier to program and its programming
"infrastructure" is MUCH MUCH more mature.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

Hi Merlin,

>> 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.

> I think your understanding is off here.  Functions can encapsulate
> arbitrary collection of statements...as I said previously, there are
> two principle differences:
> *) functions have implicit created transaction, procedures do not
> *) how you pass data to/from the procedure body.  (functions return a
> scalar, record, or a set)
>
> Functions are limited in the sense that it is awkward to return
> multiple sets, but are much more flexible how they can be integrated
> into queries -- you can call a function anywhere a scalar or a set is
> allowed -- in addition to the monolithic procedure style.

I was speaking about how it's done in MySQL. And I liked their approach
of clear separation of responsibility between functions and stored
procedures. At first I didn't quite understand their point, but then,
during the development of my app, I gradually began to appreciate it.

To sum up how it's done in MySQL:

Functions are meant for tasks such as string operations, date/calendar
functions, maths, encryption. They are allowed to operate only on their
arguments. And they are stackable, just as functions in other languages
like C.

Stored procedures are meant to be programs that work on the data.
Hence they allowed to access tables, they can start explicit
transactions and they can execute plain arbitrary SELECTs that pass
their rows straight to the client. And stored procedures are "nestable"
- akin to include() in PHP.


I suspect that the present situation with Postgres reflects the way the
software developed over the years. Perhaps in the very beginning the
Postgres developers introduced functions which more or less resembled
the "plain" functions of MySQL today. But then users might have pressed
for a method to store their table manipulation logic on the server, and
then for some reason it had been decided to overload functions with this
extra responsibility, rather than create a separate clean "stored
procedure" class.

So today Postgres has got functions which are very feature-full
(compared with functions in MySQL), but still fall short of what
traditional stored procedures can provide.

Yes, I was very much pleased with a number of Postgres features, such as
the ability to do a tighter data definition using checks and
constraints. Postgres allows for a much richer data model when I compare
it with MySQL. I decided to put Postgres aside simply because it doesn't
allow the definition of *clean* stored procedures (as I'm used to them
in MySQL). And I didn't like the idea of twisting the PG function model
around to accommodate my existing MySQL stored procedure logic. I abhor
doing ugly things with code :)


Pavel stated interest to work on the addition of stored procedures to
Postgres provided he finds sponsorship. Right now I don't see much
benefit investing money into such a venture, besides I've got my hands
full with the day-to-day management of my own project. So far MySQL has
been doing its job well and for the near future it looks like I'm
staying on it.



> Just as a 'for example', look how you can trap errors and do some
> recovery inside a pl/pgsql routine:
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Well, MySQL does allow for exception handling within SPs, although there
are some shortcomings (if you define a generic handler you cannot obtain
precise info on the error type).


> That feature alone can help you enormously.  Lest you think I'm
> biased, I dba a mysql box professionally...every time I pop into the
> mysql shell I feel like I'm stepping backwards in time about 5 years.
> Don't let the inability to return multiple sets trip you up...you are
> missing the big picture.

Oh, I am not missing the big picture: Quit programming and take up the
job of a lazy millionaire :)

> ok :-) enough advocacy...
> merlin

Cheers,

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

Vladimir Dzhuvinov wrote:

> > That feature alone can help you enormously.  Lest you think I'm
> > biased, I dba a mysql box professionally...every time I pop into the
> > mysql shell I feel like I'm stepping backwards in time about 5 years.
> > Don't let the inability to return multiple sets trip you up...you are
> > missing the big picture.
>
> Oh, I am not missing the big picture: Quit programming and take up the
> job of a lazy millionaire :)

I don't quite understand you here.  I'm sure we all crave the lazy
millionaire bit, but what would a lazy millionaire do other than
programming for fun?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On 10/15/08, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Vladimir Dzhuvinov wrote:
>
> > > That feature alone can help you enormously.  Lest you think I'm
> > > biased, I dba a mysql box professionally...every time I pop into the
> > > mysql shell I feel like I'm stepping backwards in time about 5 years.
> > > Don't let the inability to return multiple sets trip you up...you are
> > > missing the big picture.
> >
> > Oh, I am not missing the big picture: Quit programming and take up the
> > job of a lazy millionaire :)
>
> I don't quite understand you here.  I'm sure we all crave the lazy
> millionaire bit, but what would a lazy millionaire do other than
> programming for fun?
>

read dozens of mails from a forum?

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Below is a very good summary of the limitations of our function
capabilities compared to procedures, e.g.:

    o  no transaction control in functions
    o  no multi-query return values without using special syntax

I don't think we can cleanly enable the second capability, but could we
allow transaction control for functions that are not called inside a
multi-statement transaction?

FYI, right now when you call a function all statements are assumed to be
in a single transaction, and allowing transaction control inside a
function would mean that each statement in a function is its own
transaction _unless_ transaction control is specified.  There would
certainly need to be special syntax to enable this.

Is there a TODO here?

---------------------------------------------------------------------------

Vladimir Dzhuvinov wrote:
-- Start of PGP signed section.
> Hi Merlin,
>
> >> 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.
>
> > I think your understanding is off here.  Functions can encapsulate
> > arbitrary collection of statements...as I said previously, there are
> > two principle differences:
> > *) functions have implicit created transaction, procedures do not
> > *) how you pass data to/from the procedure body.  (functions return a
> > scalar, record, or a set)
> >
> > Functions are limited in the sense that it is awkward to return
> > multiple sets, but are much more flexible how they can be integrated
> > into queries -- you can call a function anywhere a scalar or a set is
> > allowed -- in addition to the monolithic procedure style.
>
> I was speaking about how it's done in MySQL. And I liked their approach
> of clear separation of responsibility between functions and stored
> procedures. At first I didn't quite understand their point, but then,
> during the development of my app, I gradually began to appreciate it.
>
> To sum up how it's done in MySQL:
>
> Functions are meant for tasks such as string operations, date/calendar
> functions, maths, encryption. They are allowed to operate only on their
> arguments. And they are stackable, just as functions in other languages
> like C.
>
> Stored procedures are meant to be programs that work on the data.
> Hence they allowed to access tables, they can start explicit
> transactions and they can execute plain arbitrary SELECTs that pass
> their rows straight to the client. And stored procedures are "nestable"
> - akin to include() in PHP.
>
>
> I suspect that the present situation with Postgres reflects the way the
> software developed over the years. Perhaps in the very beginning the
> Postgres developers introduced functions which more or less resembled
> the "plain" functions of MySQL today. But then users might have pressed
> for a method to store their table manipulation logic on the server, and
> then for some reason it had been decided to overload functions with this
> extra responsibility, rather than create a separate clean "stored
> procedure" class.
>
> So today Postgres has got functions which are very feature-full
> (compared with functions in MySQL), but still fall short of what
> traditional stored procedures can provide.
>
> Yes, I was very much pleased with a number of Postgres features, such as
> the ability to do a tighter data definition using checks and
> constraints. Postgres allows for a much richer data model when I compare
> it with MySQL. I decided to put Postgres aside simply because it doesn't
> allow the definition of *clean* stored procedures (as I'm used to them
> in MySQL). And I didn't like the idea of twisting the PG function model
> around to accommodate my existing MySQL stored procedure logic. I abhor
> doing ugly things with code :)
>
>
> Pavel stated interest to work on the addition of stored procedures to
> Postgres provided he finds sponsorship. Right now I don't see much
> benefit investing money into such a venture, besides I've got my hands
> full with the day-to-day management of my own project. So far MySQL has
> been doing its job well and for the near future it looks like I'm
> staying on it.
>
>
>
> > Just as a 'for example', look how you can trap errors and do some
> > recovery inside a pl/pgsql routine:
> > http://www.postgresql.org/docs/8.3/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING
>
> Well, MySQL does allow for exception handling within SPs, although there
> are some shortcomings (if you define a generic handler you cannot obtain
> precise info on the error type).
>
>
> > That feature alone can help you enormously.  Lest you think I'm
> > biased, I dba a mysql box professionally...every time I pop into the
> > mysql shell I feel like I'm stepping backwards in time about 5 years.
> > Don't let the inability to return multiple sets trip you up...you are
> > missing the big picture.
>
> Oh, I am not missing the big picture: Quit programming and take up the
> job of a lazy millionaire :)
>
> > ok :-) enough advocacy...
> > merlin
>
> Cheers,
>
> Vladimir
> --
> Vladimir Dzhuvinov * www.valan.net * PGP key ID AC9A5C6C
>
-- End of PGP section, PGP failed!

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Wed, Oct 15, 2008 at 5:48 PM, Bruce Momjian <bruce@momjian.us> wrote:
>
> Below is a very good summary of the limitations of our function
> capabilities compared to procedures, e.g.:
>
>        o  no transaction control in functions
>        o  no multi-query return values without using special syntax
>
> I don't think we can cleanly enable the second capability, but could we
> allow transaction control for functions that are not called inside a
> multi-statement transaction?
>
> FYI, right now when you call a function all statements are assumed to be
> in a single transaction, and allowing transaction control inside a
> function would mean that each statement in a function is its own
> transaction _unless_ transaction control is specified.  There would
> certainly need to be special syntax to enable this.
>
> Is there a TODO here?

I don't think so, except that we need a TODO for proper stored
procedure support if there is not one already.  Proper SPs have been
much discussed, Pavel spearheading what effort has been done.

Being able to manually do transactions for functions would be nice
certainly, but I suspect this is a big part of the challenge for
proper SPs.

merlin

2008/10/16 Merlin Moncure <mmoncure@gmail.com>:
> On Wed, Oct 15, 2008 at 5:48 PM, Bruce Momjian <bruce@momjian.us> wrote:
>>
>> Below is a very good summary of the limitations of our function
>> capabilities compared to procedures, e.g.:
>>
>>        o  no transaction control in functions
>>        o  no multi-query return values without using special syntax
>>
>> I don't think we can cleanly enable the second capability, but could we
>> allow transaction control for functions that are not called inside a
>> multi-statement transaction?
>>
>> FYI, right now when you call a function all statements are assumed to be
>> in a single transaction, and allowing transaction control inside a
>> function would mean that each statement in a function is its own
>> transaction _unless_ transaction control is specified.  There would
>> certainly need to be special syntax to enable this.
>>
>> Is there a TODO here?
>
> I don't think so, except that we need a TODO for proper stored
> procedure support if there is not one already.  Proper SPs have been
> much discussed, Pavel spearheading what effort has been done.
>
> Being able to manually do transactions for functions would be nice
> certainly, but I suspect this is a big part of the challenge for
> proper SPs.
>

call statement should to live outside implicit transaction, so it's
possible. It's simple in SQL/PSM, that is designed with transaction
controll management.

Pavel

> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>