possibility to force transformation procedures to functions

[okbob]

My customer reported, so there is not possibility to ensure old behaviour of transformation procedures on modern postgres.

PG_SUPPORTS_PROCEDURE is implicitly set for PostgreSQL v11+. Can be nice to have some option that forces old behaviour.

[darold]

Hi Pavel,

Normally you just have to set PG_VERSION to the version of the PostgreSQL server. If you set it to 10 then PG_SUPPORTS_PROCEDURE will be disabled.

[okbob]

po 21. 10. 2024 v 4:12 odesílatel Gilles Darold @.***> napsal:

Hi Pavel,

Normally you just have to set PG_VERSION to the version of the PostgreSQL server. If you set it to 10 then PG_SUPPORTS_PROCEDURE will be disabled.

ok. I think it can work now, because there are no extra features used by my customer like virtualcol and outparams.

Byt maybe for these feature related flags there can be some force overwrite options? It is mainly for compatibility reasons - the customer has been working on migration for more than 6 years, and too big changes of ora2pg based on PostgreSQL releases is problematic.

or PG_SUPPORTS_PROCEDURE can have three states ... "on", "off" and "auto" - and default can be "auto"

— Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/1830#issuecomment-2425415998, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO476IUEF4SV6MRDA6ADZ4RPJFAVCNFSM6AAAAABQIEKGIOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMRVGQYTKOJZHA . You are receiving this because you authored the thread.Message ID: @.***>

[darold]

Commit 0a22650 might support overriding of the PG_VERSION automatic adjustment of these PG_SUPPORT_* variables.

If they are defined in the configuration file, they take precedence onto the PG_VERSION behavior, whether they are disabled or enabled.

[okbob]

po 21. 10. 2024 v 8:57 odesílatel Gilles Darold @.***> napsal:

Commit 0a22650 https://github.com/darold/ora2pg/commit/0a22650b132fba01d0ca3eb91ec27e69afb7d1f9 might support overriding of the PG_VERSION automatic adjustment of these PG_SUPPORT_* variables.

If they are defined in the configuration file, they take precedence onto the PG_VERSION behavior, whether they are disabled or enabled.

great

Thank you very much

Pavel

— Reply to this email directly, view it on GitHub https://github.com/darold/ora2pg/issues/1830#issuecomment-2425771106, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAEFO44X5OVFECPYJD4O5ELZ4SQUBAVCNFSM6AAAAABQIEKGIOVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDIMRVG43TCMJQGY . You are receiving this because you authored the thread.Message ID: @.***>

[okbob]

My customer did some tests and, unfortunately, reported few issues:

1. Although the procedure is converted to function, it is called by CALL statement instead SELECT
2. Transformed function use strange OUT extra_param OPAQUE argument. OPAQUE pseudotype was removed in PostgreSQL 12

Oracle:

create or replace procedure CO_ZNAK (
mINTERFS IN VARCHAR2,
mCISLO in OUT NUMBER ,
mTEXT in OUT VARCHAR2 )

IS

--i NUMBER;
 
--akt NUMBER;
mZnak CHAR(1);


begin
   if mINTERFS is not NULL then
   ---
   FOR i IN 1..LENGTH(mINTERFS)
   LOOP
     mZnak := SUBSTR(mINTERFS , i,1 );

    
    IF mZnak IN ( '0','1','2','3','4','5','6','7','8','9','0' ) THEN    
        mCISLO :=TO_NUMBER(TO_CHAR(mCISLO)||mZnak);     
     else   
       mTEXT :=mTEXT||mZnak;     
     end if;  
     
   END LOOP;
   end if;
   
end CO_ZNAK;

Older ora2pg

CREATE OR REPLACE FUNCTION co_znak ( mINTERFS text, mCISLO INOUT numeric , mTEXT INOUT text ) AS $body$
DECLARE


--i NUMBER;
 
--akt NUMBER;
mZnak char(1);


BEGIN
   if mINTERFS is not NULL then
   ---
   FOR i IN 1..LENGTH(mINTERFS)
   LOOP
     mZnak := SUBSTR(mINTERFS , i,1 );


    IF mZnak IN ( '0','1','2','3','4','5','6','7','8','9','0' ) THEN    
        mCISLO :=(TO_CHAR(mCISLO)||mZnak)::numeric;
     else   
       mTEXT :=mTEXT||mZnak;
     end if;
     
   END LOOP;
   end if;

end;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

Current Ora2pg

CREATE OR REPLACE FUNCTION co_znak ( mINTERFS text, mCISLO INOUT bigint , mTEXT INOUT text , OUT extra_param OPAQUE) RETURNS record AS $body$
DECLARE


--i NUMBER;
 
--akt NUMBER;
mZnak char(1);


BEGIN
   if (mINTERFS AND mINTERFS::text <> '') then
   ---
   FOR i IN 1..LENGTH(mINTERFS)
   LOOP
     mZnak := SUBSTR(mINTERFS , i,1 );


    IF mZnak IN ( '0','1','2','3','4','5','6','7','8','9','0' ) THEN    
        mCISLO :=(TO_CHAR(mCISLO)||mZnak)::numeric;
     else   
       mTEXT :=mTEXT||mZnak;
     end if;
     
   END LOOP;
   end if;

end;

$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
;

[darold]

Thanks Pavel. Commit 4d1696c might solve this issue.

[okbob]

I have check report from my customer. He found still one issue. When procedure has more OUT arguments, then migration is ok, but when procedure has just one OUT argument, then there is a problem, because ora2pg produces function like

CREATE OR REPLACE FUNCTION akt_abo_skupina ( mID_PARABOVYS PARABOVYS.ID_PARABOVYS%TYPE , mPocChyb OUT numeric ) RETURNS record AS $body$

And this doesn't work

(2024-12-03 18:57:47) postgres=# create or replace function foo(x out int) returns int as $$ begin x := 10; end $$ language plpgsql;
CREATE FUNCTION
(2024-12-03 18:58:25) postgres=# select foo();
┌─────┐
│ foo │
╞═════╡
│  10 │
└─────┘
(1 row)

(2024-12-03 18:58:32) postgres=# create or replace function foox(x out int) returns record as $$ begin x := 10; end $$ language plpgsql;
ERROR:  function result type must be integer because of OUT parameters

[darold]

Hi Pavel,

Here is an Oracle function with a single out parameter and e return value:

function fct_test(p1 varchar, p2 out number) return number
as 
DECLARE
        v1 number := 100;
BEGIN
  p2 := 10;
  return v1;
END fct_test;

It is correctly translated by ora2pg into

CREATE OR REPLACE FUNCTION fct_test (p1 text, p2 out bigint, OUT extra_param bigint) RETURNS record AS $body$
DECLARE

        v1 bigint := 100;

BEGIN
  p2 := 10;
  extra_param := v1;
  RETURN;
END;
$body$
LANGUAGE PLPGSQL
 STABLE;

Then when using it it returns the right result:

gilles=# DO $$                                                                                                        
DECLARE
    a bigint;
    b bigint;
BEGIN
    SELECT * FROM fct_test('hello') INTO a, b;
    RAISE NOTICE 'a=%, b=%', a, b;
END;                
$$;
NOTICE:  a=10, b=100
DO

[darold]

Note that I have fix a wrong translation of OUT to INOUT in new commit cbb61c6

[darold]

With this commit an Oracle function defined as follow:

function fct_test(p2 out number)
as 
BEGIN 
  p2 := 10;
END fct_test;

is translated into

CREATE OR REPLACE FUNCTION fct_test (p2 out bigint) AS $body$
BEGIN
  p2 := 10;
END;
$body$
LANGUAGE PLPGSQL
 STABLE;

which returns the expected result:

gilles=# DO $$                   
DECLARE
a bigint;
BEGIN
SELECT * FROM fct_test() INTO a;
RAISE NOTICE 'a=%', a;
END;
$$;
NOTICE:  a=10
DO

[okbob]

Unfortunately, it is not fixed (on procedures). It was not when Oracle's procedures are converted to postgres functions.

[darold]

Fixed with #1850 if I have well understood the remaining issue here.