Re: Support for OUT parameters in procedures

On 2020-09-29 08:23, Pavel Stehule wrote:
> This was an important issue if I remember well.  Passing mandatory NULL 
> as OUT arguments solves this issue.
> I fully agree so OUT arguments are part of the procedure's signature. 
> Unfortunately, there is another difference
> from functions, but I don't think so there is a better solution, and we 
> should live with it. I think it can work well.

This has been committed.

> I found one issue. The routine for selecting function or procedure based 
> on signature should be fixed.
> 
> CREATE OR REPLACE PROCEDURE public.procp(OUT integer)
>   LANGUAGE plpgsql
> AS $procedure$
> BEGIN
>    $1 := 10;
> END;
> $procedure$
> 
> DO
> $$
> DECLARE n numeric;
> BEGIN
>    CALL procp(n);
>    RAISE NOTICE '%', n;
> END;
> $$;
> ERROR:  procedure procp(numeric) does not exist
> LINE 1: CALL procp(n)
>               ^
> HINT:  No procedure matches the given name and argument types. You might 
> need to add explicit type casts.
> QUERY:  CALL procp(n)
> CONTEXT:  PL/pgSQL function inline_code_block line 4 at CALL

This is normal; there is no implicit cast from numeric to int.  The same 
error happens if you call a function foo(int) with foo(42::numeric).

> postgres=# create or replace procedure px(anyelement, out anyelement)
> as $$
> begin
>    $2 := $1;
> end;
> $$ language plpgsql;
> 
> postgres=# call px(10, null);
> ERROR:  cannot display a value of type anyelement
> 
> but inside plpgsql it works
> do $$
> declare xx int;
> begin
>    call px(10, xx);
>    raise notice '%', xx;
> end;
> $$;

This might be worth further investigation, but since it happens also 
with INOUT parameters, it seems orthogonal to this patch.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services