1

Is it possible to debug pl/pgsql procedures in DBever with input and output parameters?

My procedure

CREATE OR REPLACE PROCEDURE identity.identity_operations
(
    IN operation_ character varying, 
    IN data_ jsonb, 
    OUT output_ jsonb
) LANGUAGE plpgsql
AS $procedure$

How I try to debug

I can't start debugging because I am passing 2 values ​​even though my procedure requires 2 input and 1 output parameter

I can't start debugging because I am passing 2 values ​​even though my procedure requires 2 input and 1 output parameter, and I get this error text:

PostgreSQL Debug - Local session 11024
ERROR: procedure identity.identity_operations(unknown, unknown) does not exist
Hint: No procedure with the given name and argument types was found. You may need to add explicit type casts.
Position: 6

or should I use out parameter as inout?

3
  • You cannot debug a procedure that was not found, please read the Hint: "No procedure with the given name and argument types was found" ) Commented Sep 15, 2024 at 9:42
  • When the procedure is found, you can do this in DBeaver Pro, see: dbeaver.com/docs/dbeaver/PGDebugger/7.3 Commented Sep 15, 2024 at 9:44
  • You still need to pass all 3 params to match the function signature. It's just that Postgres will take the 3rd one, overwrite it and return that. If you want to do calls that skip the 3rd param, you'd have to switch it from just out to inout and attach a default - then a 2-param call will be accepted: demo. Commented Sep 15, 2024 at 10:46

1 Answer 1

1

You still need to pass all 3 params to match the function signature even though the last one's value will be completely ignored, only its type being important. From the CALL doc:

Arguments must be supplied for all procedure parameters that lack defaults, including OUT parameters. However, arguments matching OUT parameters are not evaluated, so it's customary to just write NULL for them. (Writing something else for an OUT parameter might cause compatibility problems with future PostgreSQL versions.)

demot at db<>fiddle

create procedure p(in a1 text,in a2 text,out a3 text)language plpgsql as $p$
begin
  a3:='hello';
end $p$;

call p('a1_v','a2_v');--will not work
ERROR:  procedure p(unknown, unknown) does not exist
LINE 1: call p('a1_v','a2_v');--will not work
             ^
HINT:  No procedure matches the given name and argument types. You might need to add explicit type casts.

As soon as you pass anything as the 3rd param, the procedure will be recognised just fine, as long as the type matches:

call p('a1_v','a2_v',null::text);--ok
a3
hello

As per the quote earlier, if you want to do calls that skip the 3rd param, you need to switch it from just out to inout and add a default - then a 2-param call will be accepted:

drop procedure p(text,text,text);
create procedure p(in a1 text,in a2 text,inout a3 text default null)
language plpgsql as $p$
begin
  a3:='hello';
end $p$;

call p('a1_v','a2_v');--now the default handles the skipped param
a3
hello
call p('a1_v','a2_v','');--still works
a3
hello
call p('a1_v','a2_v','a3_v');--still works
a3
hello
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.