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).
this is OUT argument - so direction is reversed - and implicit cast from int to numeric exists.
> 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.
yes - this breaks using varchar against text argument, although these types are almost identical.
-- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services