2011/5/10 Robert Haas <robertmhaas@gmail.com>:
> On Mon, May 9, 2011 at 11:58 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>> no - you are little bit confused :). CALL and function execution
>> shares nothing. There is significant differences between function and
>> procedure. Function is called only from executor - from some plan, and
>> you have to know a structure of result before run. The execution of
>> CALL is much simple - you just execute code - without plan and waiting
>> for any result - if there is.
>
> Now I'm a little confused, or you are. Surely any SQL has to be
> planned and executed, regardless of whether it appears in a function,
> a stored procedure, or anywhere else. Non-SQL statements within a
> stored procedure don't need to go through the planner and executor,
> but that's true in PL/python or PL/pgsql or whatever today.
>
CALL statement is "util command" than SQL. It has to execute some NON SQL code.
You can thinking about CALL statement like synonymum for SELECT, but
it isn't correct (it is my opinion)
The "stored procedures" was prior stored functions (more corectly UDF
- user defined functions). These "old time" stored procedures was
simply - it was client code moved on server. Usually these procedures
was executed in different process or different thread. Inside
procedures was full client's side functionality and there wasn't a
network overhead. CALL statement is +/- remote call. It isn't SQL
statement.
> I think people are using the term "stored procedures" to refer to
> approximately whatever it is that they're unhappy that functions don't
> allow, and that's leading to a lot of people talking across each
> other. The main features seem to be (1) explicit transaction control
> and/or execution of commands like VACUUM that can't be invoked from
> within a transaction, (2) autonomous transactions, and (3) returning
> multiple result sets. But I don't think anybody would be desperately
> unhappy if it magically became possible to do those things from
> regular functions, unlikely as that may seem.
>
yes.
@2 Autonomous transaction doesn't need stored procedures. Autonomous
transaction can be isolated by function's flag, by some special
PL/pgSQL statement:
like
BEGIN EXECUTE AUTONOMOUS '....'
END;
@3 is possible now too - but not too much user friendly. Point 3 is
strange. Oracle doesn't support it. Support in DB2 is little bit
strange. And it is well supported by MySQL, MSSQL, maybe Informix,
Sybase.
Pavel
Regards
Pavel
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
