I'm trying to run natively correct plpgsql code in EDB environment. Unfortunately there is a problem. Interestingly, the first run does not generate an error. Restarts generate an error, but after a few repetitions, there is no error again. In PosgreSQL, the syntax DOES NOT CAUSE any problems. Below is an example:
CREATE OR REPLACE PROCEDURE test1()
AS $procedure$
DECLARE
BEGIN
null;
END;
$procedure$
LANGUAGE plpgsql
;
CREATE OR REPLACE PROCEDURE test2()
AS $procedure$
DECLARE
BEGIN
call test1();
END;
$procedure$
LANGUAGE plpgsql
;
CREATE OR REPLACE PROCEDURE test3()
AS $procedure$
DECLARE
BEGIN
call test2();
END;
$procedure$
LANGUAGE plpgsql
;
And now try to run it in postgresql way and EDB way:
--run it few times as edb - error occurs randomly
begin
test3();
end;
--once again as plpgs - no error occurs but... check last call
do
$$
begin
CALL test3();
end;
$$
--once again as plpgs with exception block. - now error occurs same as edb call
do
$$
declare
v_sqlstate text;
v_message text;
v_context text;
begin
CALL test3();
EXCEPTION
WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_sqlstate = returned_sqlstate,v_message = message_text,v_context = pg_exception_context;
RAISE NOTICE 'sqlstate: %,message: %,context: %', v_sqlstate,v_message,v_context;
end;
$$
Error is:
ERROR: SPL procedure with SPL-style OUT parameter or a function cannot be invoked using CALL in PL/pgSQL
HINT: You might want to use SELECT instead.
CONTEXT: PL/pgSQL function test3() line 4 at CALL
edb-spl function inline_code_block line 2 at procedure/function invocation statement
SQL state: 42809
What am I missing???
I find that this might be a bug fixed in EPAS 13.8 (DB-1818). https://www.enterprisedb.com/docs/epas/13/epas_rel_notes/epas13_8_12_rel_notes/
I guess you may try your test in this or later new minor version.
Best Regards.