I do not have pg 13.6 installed yet as we just upgraded all of our systems from 11 to 13.5 but I suspect it will be the same.
Here is the test script:
select version();
create or replace procedure foo ( inout p_inout_parameter character varying default null::character varying)
language plpgsql
as $procedure$
declare
lv_this_goes_out character varying;
begin
lv_this_goes_out := 'I am the walrus';
raise notice 'foo called';
p_inout_parameter := lv_this_goes_out;
end;
$procedure$;
create or replace procedure bar ()
language plpgsql
as $procedure$
declare
lv_somestring character varying (4000);
begin
call foo(lv_somestring::character varying); -- note cast
raise notice 'lv_somestring: %', lv_somestring;
end;
$procedure$;
-- this works in pg11 and fails in pg13
do $$
declare l_var text;
begin
call bar ();
end;
$$;
drop routine if exists foo;
drop routine if exists bar;
Expected output: (pg 11.13)
version
------------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 11.13 (Ubuntu 11.13-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)
CREATE PROCEDURE
CREATE PROCEDURE
psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:32: NOTICE: foo called
psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:32: NOTICE: lv_somestring I am the walrus
DO
DROP ROUTINE
DROP ROUTINE
Actual output: pg 13.15
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.5 (Ubuntu 13.5-2.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
(1 row)
CREATE PROCEDURE
CREATE PROCEDURE
psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:32: ERROR: procedure parameter "p_inout_parameter" is an output parameter but corresponding argument is not writable
CONTEXT: PL/pgSQL function bar() line 5 at CALL
SQL statement "CALL bar ()"
PL/pgSQL function inline_code_block line 4 at CALL
psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:33: ERROR: current transaction is aborted, commands ignored until end of transaction block
psql:/Users/edelaney/Desktop/boxsync/wip/typecast-arguments-bug.sql:34: ERROR: current transaction is aborted, commands ignored until end of transaction block
While no explicit cast is actually required, we have a very large amount of generated code that uses explicit type casting this way.
Ed Delaney (he/him) | Principal Architect
