On 12/16/18 11:33 AM, Anton Shen wrote:
Thanks for the thoughts. The part I'm missing is that why procedures
with OUT param 'will not be called from SQL environments'?
Pretty sure Pavel was referring to:
https://www.postgresql.org/docs/11/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS
"Notice that output parameters are not included in the calling argument
list when invoking such a function from SQL. This is because PostgreSQL
considers only the input parameters to define the function's calling
signature. ..."
From this commit:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e4128ee767df3c8c715eb08f8977647ae49dfb59
"SQL procedures
This adds a new object type "procedure" that is similar to a function
but does not have a return type and is invoked by the new CALL statement
instead of SELECT or similar.
...
While this commit is mainly syntax sugar around existing functionality,
future features will rely on having procedures as a separate object
type."
I read this to mean that since SQL functions don't have OUT in the
signature at this time, SQL procedures do not either.
Thanks,
Anton
On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule <pavel.stehule@xxxxxxxxx
<mailto:pavel.stehule@xxxxxxxxx>> wrote:
Hi
út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175george@xxxxxxxxx
<mailto:4175george@xxxxxxxxx>> napsal:
Hi all,
I was playing around with the stored procedure support in v11
and found that pure OUT parameters are not supported. Is there
any reason we only support INOUT but not OUT parameters?
The procedure implementation in v11 is initial stage - only
functionality with some simple implementation or without design
issues was implemented.
If I remember there was not clean what is correct and expected
behave of usage of OUT variable when it is called from SQL
environment, and when it is called from plpgsql.
On Oracle - the OUT variables are part of procedure signature - you
can write procedures P1(OUT a int), P1(OUT a text). Currently we
have not a variables in SQL environment. So if Peter implemented OUT
variables now then
a) only IN parameters will be part of signature - like functions -
but it is different than on Oracle, and we lost a possibility to use
interesting feature
b) the procedures with OUT variables will not be callable from SQL
environment - that be messy for users.
c) disallow it.
I hope so PostgreSQL 12 will have schema variables, and then we can
implement OUT variables. Now, it is not possible (do it most
correct) due missing some other feature. INOUT parameters are good
enough, and we have opened door for future correct design.
Regards
Pavel
psql (11.0 (Homebrew petere/postgresql))
dev=# CREATE PROCEDURE test_sp(a OUT int) LANGUAGE plpgsql AS $$
dev$# BEGIN
dev$# a = 5;
dev$# END; $$;
ERROR: procedures cannot have OUT arguments
HINT: INOUT arguments are permitted.
Thanks,
Anton
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
