I have a stored procedure with an INOUT parameter, which value is modified inside the SP. I need to call that SP, providing a variable as that parameter, and then use the modified variable. But the tricky part is that I need to call the SP using dynamic SQL. When I do that, the variable's value is not changed:
CREATE OR REPLACE PROCEDURE pr_test( INOUT p_rows INTEGER DEFAULT NULL ) AS $body$ BEGIN RAISE NOTICE 'Inside SP, p_rows before modify = %', p_rows; p_rows := 100; RAISE NOTICE 'Inside SP, p_rows after modify = %', p_rows; RETURN; END $body$ LANGUAGE 'plpgsql' ;
Trying to use it...
DO $$ DECLARE v INTEGER = 7; v_sql TEXT; BEGIN RAISE NOTICE 'v before SP call = %', v; v_sql := 'CALL pr_test(p_rows => $1);'; EXECUTE v_sql USING v; RAISE NOTICE 'v after SP call = %', v; END;$$;
And the output is:
v before SP call = 7 Inside SP, p_rows before modify = 7 Inside SP, p_rows after modify = 100 v after SP call = 7
Please advise on how to do it right.