Thread: new stored procedure with OUT parameters

new stored procedure with OUT parameters

From

Anton Shen

Date:

11 December 2018, 09:19:51

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?

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

Re: new stored procedure with OUT parameters

From

Pavel Stehule

Date:

15 December 2018, 21:02:55

út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175george@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

Re: new stored procedure with OUT parameters

From

Anton Shen

Date:

16 December 2018, 22:33:24

Thanks for the thoughts. The part I'm missing is that why procedures with OUT param 'will not be called from SQL environments'?

Thanks,

Anton

On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175george@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

Re: new stored procedure with OUT parameters

From

Pavel Stehule

Date:

16 December 2018, 22:50:50

ne 16. 12. 2018 v 20:33 odesílatel Anton Shen <4175george@gmail.com> napsal:

Thanks for the thoughts. The part I'm missing is that why procedures with OUT param 'will not be called from SQL environments'?

PostgreSQL, Oracle has function/procedure overloading. The function signature - that is unique for any function is composed from all parameters (on Oracle) or from only IN parameters (on PostgreSQL).

On Oracle I can have a procedures P(IN int, OUT varchar2), P(IN int, OUT number) - it is not possible on Postgres, because OUT parameters are not part of signature there. There is some workaround with passing fake variable like P(IN int, IN varchar, OUT varchar), P(IN int, IN numeric, OUT numeric). It is not nice, but there is not any hope to fix it due ensuring compatibility.

The procedures are new - and now, there are time to define behave. You can see, so INOUT parameters are passed to procedure similar to Oracle.

Because Peter didn't allow OUT variables, then he didn't need to solve a question what parameters are part of signature. IN, INOUT parameters are from signature perspective IN parameters - so it is nothing new.

SQL environment has not any variables (now). So you cannot to use any OUT parameter from this environment - and then has not sense to use signature with OUT parameters. But it is possible from PL/pgSQL - and it is not consistent.

Thanks,
Anton

On Sat, Dec 15, 2018 at 10:03 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

út 11. 12. 2018 v 7:20 odesílatel Anton Shen <4175george@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

Re: new stored procedure with OUT parameters

From

Adrian Klaver

Date:

16 December 2018, 23:05:19

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

Re: new stored procedure with OUT parameters

From

Anton Shen

Date:

19 December 2018, 21:22:41

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