Re: INOUT parameters in procedures

On Tue, Mar 20, 2018 at 6:38 AM, Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> On 3/19/18 03:25, Rushabh Lathia wrote:
> > For the FUNCTION when we have single OUT/INOUT parameter
> > the return type for that function will be set to the type of OUT
> parameter.
> > But in case of PROCEDURE, it's always RECORDOID, why this inconsistency?
>
> For procedures, this is just an implementation detail. The CALL command
> returns a row in any case, so if we set the return type to a scalar
> type, we'd have to add special code to reassemble a row anyway. For
> functions, the inconsistency is (arguably) worth it, because it affects
> how functions can be written and called, but for procedures, there would
> be no point.
>
>
This feel like inconsistency with the existing system object FUNCTION.
It would be nice to be consistent with the FUNCTION - which set the
prorettype as the type of single IN/OUT in case of single argument.

If CALL command returns a row in any case, then I think adding logic
to build row while building the output for CALL statement make more sense.

> > Above test throws an error saying calling procedures with output
> > arguments are not supported in SQL functions. Whereas similar test
> > do work with SQL functions:
>
> This was discussed earlier in the thread.
>
> The behavior of output parameters in functions was, AFAICT, invented by
> us. But for procedures, the SQL standard specifies it, so there might
> be some differences.
>
>
Sorry, but I am still unable to understand the difference.
In case of PROCEDURE, it's calling the PROCEDURE with out parameter.
So if that we call the same PROCEURE in the psql prompt:

postgres(at)101361=#CALL ptest4a(null, null);
a | b
---+---
1 | 2
(1 row)

and same is the case if we call the FUNCTION in the psql prompt:

postgres(at)101361=#SELECT * from ftest4b(null, null);
b | a
---+---
1 | 2
(1 row)

So if I understand correctly, in the testcase where it's calling the CALL
within SQL procedure - has to throw similar output. Isn't it?

> ERROR: calling procedures with output arguments is not supported in SQL
> > functions
> > CONTEXT: SQL function "ptest4b"
> >
> > Here error message says that calling procedures with output arguments is
> not
> > supported in SQL functions. Whereas here it's getting called from the
> SQL
> > procedure. So error message needs to be changed.
>
> Well, I don't think we are going to change every single error message
> from "function" to a separate function and procedure variant.
>
>
I think we should, otherwise it pass the wrong message to the user. Like
here it says "calling procedures with output arguments is not supported in
SQL functions"
but actually test is calling the procedures from procedure. I think now
that
we have a way to ideintify FUNCTION/PROCEDURE (prokind) it's good
to give proper error message.

Recently commit 2c6f37ed62114bd5a092c20fe721bd11b3bcb91e and
8b9e9644dc6a9bd4b7a97950e6212f63880cf18b replace AclObjectKind and
GrantObjectType with ObjectType and with that we now getting proper
object type for the acl error message. In case of PROCEDURE
and FUNCTIONS also error message should send clear message.

Regards,
Rushabh Lathia
www.EnterpriseDB.com