Re: new stored procedure with OUT parameters

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(dot)klaver(at)aklaver(dot)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(dot)stehule(at)gmail(dot)com
> > <mailto:pavel(dot)stehule(at)gmail(dot)com>> wrote:
> >
> > Hi
> >
> > út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175george(at)gmail(dot)com
> > <mailto:4175george(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com
>