Re: "stored procedures"

On Mon, Apr 25, 2011 at 1:18 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On tor, 2011-04-21 at 18:24 +0300, Peter Eisentraut wrote:
>> So the topic of "real" "stored procedures" came up again.  Meaning a
>> function-like object that executes outside of a regular transaction,
>> with the ability to start and stop SQL transactions itself.
>
> I would like to add a note about the SQL standard here.
>
> Some people have been using terminology that a "function" does this and
> a "procedure" does something else.  Others have also mentioned the use
> of a CALL statement to invoke procedures.
>
> Both procedures (as in CREATE PROCEDURE etc.) and the CALL statement are
> specified by the SQL standard, and they make no mention of any
> supertransactional behavior or autonomous transactions for procedures.
> As far as I can tell, it's just a Pascal-like difference that functions
> return values and procedures don't.
>
> So procedure-like objects with a special transaction behavior will need
> a different syntax or a syntax addition.

hm. does the sql standard prohibit the use of extra transactional
features? are you sure it's not implied that any sql (including
START TRANSACTION etc) is valid? meaning, unless otherwise specified,
you should be able to do those things, and that our functions because
they force one transaction operation are non-standard, not the other
way around.

merlin