Thanks Peter for working on this. Sorry for the delay in raising this
questions.
1)
@@ -302,7 +304,9 @@ interpret_function_parameter_list(ParseState *pstate,
/* handle output parameters */
if (fp->mode != FUNC_PARAM_IN && fp->mode != FUNC_PARAM_VARIADIC)
{
- if (outCount == 0) /* save first output param's type */
+ if (objtype == OBJECT_PROCEDURE)
+ *requiredResultType = RECORDOID;
+ else if (outCount == 0) /* save first output param's type */
*requiredResultType = toid;
outCount++;
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?
postgres@39755=#select proname, prorettype from pg_proc where proname =
'foo';
proname | prorettype
---------+------------
foo | 23
(1 row)
postgres@39755=#CREATE PROCEDURE foo_pro(INOUT a int)
LANGUAGE plpgsql
AS $$
begin
SELECT 1 into a;
end;$$;
CREATE PROCEDURE
postgres@39755=#select proname, prorettype from pg_proc where proname =
'foo_pro';
proname | prorettype
---------+------------
foo_pro | 2249
(1 row)
2) Inconsistency in procedure behavior - compared to function.
drop procedure ptest4a;
drop procedure ptest4b;
CREATE PROCEDURE ptest4a(INOUT a int, INOUT b int)
LANGUAGE plpgsql
AS $$
begin
SELECT 1, 2 into a, b;
end;$$;
CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
LANGUAGE SQL
AS $$
CALL ptest4a(a, b);
$$;
ERROR: calling procedures with output arguments is not supported in SQL
functions
CONTEXT: SQL function "ptest4b"
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:
CREATE FUNCTION ftest4a(INOUT a int, INOUT b int) returns record
LANGUAGE plpgsql
AS $$
begin
SELECT 1, 2 into a, b;
end;$$;
CREATE FUNCTION ftest4b(INOUT b int, INOUT a int) returns record
LANGUAGE SQL
AS $$
SELECT ftest4a(a, b);
$$;
postgres@39755=#SELECT ftest4b(null, null);
ftest4b
---------
(1,2)
(1 row)
3)
CREATE PROCEDURE ptest4b(INOUT b int, INOUT a int)
LANGUAGE SQL
AS $$
CALL ptest4a(a, b);
$$;
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.
Thanks,
Rushabh Lathia
www.EnterpriseDB.com
