How to call a stored procedure in another procedure and use the result of it in the same procedure of Postgresql?

user_10221025 0
Viewed 3168

I want to use the inout return in my procedure like this:

do $$ 
declare
film_count text;
begin
-- get the number of films
 CALL abc('999-M-120-20200906-E726265', 
 '911120006710', '120', null);
  film_count = o_ricode;
   -- display a message
   raise notice 'The number of films is %', film_count;
end $$;

where o_ricode is inout param of proc 'abc'.

ERROR: procedure parameter "o_ricode" is an output parameter but the corresponding argument is not writable CONTEXT: PL/pgSQL function inline_code_block line 6 at CALL SQL state: 42601

Calling abc procedure separately giving the expected results but calls in another procedure not working.

CREATE OR REPLACE PROCEDURE abc(
INOUT o_ricode text)
LANGUAGE 'plpgsql'
 AS $BODY$
DECLARE

BEGIN
 o_ricode := 'SUCCESS';
END;
$BODY$;
plpgsql postgresql
1 Answers

The message is clean - you can use only variables on positions of INOUT arguments. Procedure is not a function, and the function is not the procedure.

CREATE PROCEDURE proc(INOUT arg1 text)
AS $$
BEGIN
  RAISE NOTICE 'arg1 = %', arg1;
  arg1 := 'xxxx';
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE var text;
BEGIN
   -- CALL proc('AHOJ'); -- this is your issue
   var := 'AHOJ';
   -- procedure invocation and passing value by ref
   -- in this context, the procedure has not result,
   -- but it can have side effects (outer variables
   -- used as INOUT arguments can be changed)
   CALL proc(var);
   RAISE NOTICE 'var = %', var;
END;
$$;

NOTICE:  arg1 = AHOJ
NOTICE:  var = xxxx
DO

Procedures and functions (in Postgres) has different mechanism of passing INOUT variables. For procedures Postgres emulates passing value by reference (it is only emulation). Passing values (and taking result) is significantly different for functions.

CREATE FUNCTION func(INOUT arg1 text)
AS $$
BEGIN
  RAISE NOTICE 'arg1 = %', arg1;
  arg1 := 'xxxx';
END;
$$ LANGUAGE plpgsql;

DO $$
DECLARE var text;
BEGIN
   -- function execution, argument is passed as value,
   -- and the variable has assigned the result of function
   var := func('AHOJ');
   RAISE NOTICE 'var = %', var;
END;
$$;

NOTICE:  arg1 = AHOJ
NOTICE:  var = xxxx
DO
user_4066910
Related