How to migrate from oracle to postgre using "WITH RECURSIVE" and "REGEXP_SUBSTR"?

Ekz0 0
Viewed 26

There is code on oracle, how to port it to postgre without increasing it much?

                       SELECT pl.rd,
          pl.rs,
          pl.agency_plan,
          pl.o__id
     FROM ALIS.PLAF pl
     WHERE RD BETWEEN to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy') AND last_day(to_date('01.'||pMM||'.'||pYYYY, 'dd.mm.yyyy'))
       AND RS IN (SELECT TO_CHAR(REGEXP_SUBSTR(str, '[^,]+', 1, LEVEL)) RS
           FROM (SELECT TO_CHAR(P_RS_LIST) str
               FROM dual) t CONNECT BY INSTR(str, ',', 1, LEVEL - 1) > 0);
sql postgresql
1 Answers

When migrating a database to another (or really any other migration) an absolutely essential step is to analyze in detail what the old system is doing then translate into the new. A good read on this: Migrate your mindset. So what is the Oracle doing? First the section beginning and rs in. The following selects take a comma separated string (P_RS_LIST) and parses it into a set of individual elements to construct the in list. A Postgres equivalent being the string_to_table() function. Now for the section where rd between .... This section first creates a date of the specified year (pYYYY) and month (pMM) then uses the last_date() function to get the last day of that month. Postgres does not have that function, but the same result is achieved by adding interval - '1 month - 1 day' to the same derived date. Postgres could use to_date() to get the date but I find the make_date() function more convenient. So make_date(pYYYY, pMM,1) and make_date(pYYYY, pMM,1) + interval '1 month - 1 day' gets the same respective dates. Putting it all back together we get the query:

select * 
  from plaf  
 where rd between  make_date(pyyyy, pmm, 1)
              and (make_date(pyyyy, pmm, 1) + interval '1 month - 1 day')::date
  and rs in (select string_to_table(p_rs_list,','));

I hope that does not increase it too much.
The names p... seems to imply this Oracle query runs within a procedure/function. If this is the case you can create a Postgres function as:

create or replace function match_plaf( pyyyy     integer
                                     , pmm       integer
                                     , p_rs_list text
                                     ) 
    returns setof plaf
   language sql
as $$
   select * 
     from plaf  
    where rd between  make_date(pyyyy, pmm, 1)
                 and (make_date(pyyyy, pmm, 1) + interval '1 month - 1 day')::date
      and rs in (select string_to_table(p_rs_list,','))
     ;
$$;

Note Not tested as no test data supplied.

Belayer0
Related