Thank you Pavel, Adrian! That makes a lot of sense. I wasn't aware that in Oracle you can overload a procedure by its OUT parameters. I had thought in Postgres procedure overloading would definitely be the same as function overloading. Looks like the door is still open.
Regards, Anton On Sun, Dec 16, 2018 at 12:05 PM Adrian Klaver <adrian.kla...@aklaver.com> wrote: > 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.steh...@gmail.com > > <mailto:pavel.steh...@gmail.com>> wrote: > > > > Hi > > > > út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175geo...@gmail.com > > <mailto:4175geo...@gmail.com>> 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.kla...@aklaver.com >
