Search code examples
oracle-databasepostgresqldatabase-migrationplpgsql

Converting function from Oracle to PostgreSQL


I am working on converting something from Oracle to PostgreSQL. In the Oracle file there is a function:

instr(string,substring,starting point,nth location)

or as it is in my file

instr(string,chr(10),instr(string,substring),1)

In PostgreSQL this does not exist, so I looked up an equivalent function. I found:

position(substring in string)

but this does not allow the starting position and the nth location parameters.

Is there anyway to make this function start at a given point? Or is there a better function to use in PostgreSQL where I can specify starting position and the nth location?

This would have to work on PostgreSQL 8.2.15 because that is the version we are running on the database.


Solution

  • The function strpos(str, sub) in Postgres is equivalent of instr(str, sub) in Oracle. Unfortunately, the function does not have third and fourth parameters, so the expression in Postgres must be more complex.

    The function substr(str, n) gives a substring of str starting from n position.

    instr(str, ch, instr(str, sub), 1);                               --oracle
    strpos(substr(str, strpos(str, sub)), ch) + strpos(str, sub) - 1; --postgres
    

    As instr() is a powerful function I wrote it in plpgsql for my own needs.

    create or replace function instr(str text, sub text, startpos int = 1, occurrence int = 1)
    returns int language plpgsql immutable
    as $$
    declare 
        tail text;
        shift int;
        pos int;
        i int;
    begin
        shift:= 0;
        if startpos = 0 or occurrence <= 0 then
            return 0;
        end if;
        if startpos < 0 then
            str:= reverse(str);
            sub:= reverse(sub);
            pos:= -startpos;
        else
            pos:= startpos;
        end if;
        for i in 1..occurrence loop
            shift:= shift+ pos;
            tail:= substr(str, shift);
            pos:= strpos(tail, sub);
            if pos = 0 then
                return 0;
            end if;
        end loop;
        if startpos > 0 then
            return pos+ shift- 1;
        else
            return length(str)- length(sub)- pos- shift+ 3;
        end if;
    end $$;
    

    Some checks (Examples from OLAP DML Functions):

    select instr('Corporate Floor', 'or', 3, 2);  -- gives 14
    select instr('Corporate Floor', 'or', -3, 2); -- gives 2
    

    There is no reverse() function in Postgres 8.2. You can use this:

    -- only for Postgres 8.4 or earlier!
    create or replace function reverse(str text)
    returns text language plpgsql immutable
    as $$
    declare
        i int;
        res text = '';
    begin
        for i in 1..length(str) loop
            res:= substr(str, i, 1) || res;
        end loop;
        return res;
    end $$;
    

    Note. Postgres 15 introduced the regexp_instr() function.

    regexp_instr ( string text, pattern text [, start integer [, N integer [, endoption integer [, flags text [, subexpr integer ] ] ] ] ] ) → integer