Convert function from oracle to postgreSQL

Question

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

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

or like this in my file

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

This does not exist in PostgreSQL, so I was looking for an equivalent function. I found:

position(substring in string)

but this does not allow the use of the initial position and nth location parameters.

Is there any way to run this function at a given point? Or is there a better function to use in PostgreSQL where I can specify the starting position and nth place?

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

Answer 1

strpos(str, sub) Postgres instr(str, sub) Oracle. , , Postgres .

substr(str, n) str, n.

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

instr() , plpgsql .

create or replace function instr(str text, sub text, startpos int = 1, occurrence int = 1)
returns int language plpgsql
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)- pos- shift+ 1;
    end if;
end $$;

( OLAP DML):

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

Postgres 8.2 reverse(). :

-- only for Postgres 8.4 or earlier!
create or replace function reverse(str text)
returns text language plpgsql
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 $$;

Answer 2

:

instr(string, substring) ::= strpos(string, substring)

position:

position:

instr(string, substring, position) ::= strpos(substr(string, position), substring) + position - 1

position:

instr(string, substring, position) ::= strpos(substr(string, char_length(string) + position + 1), substring) + char_length(string) + position

occurrence:

PostgreSQL. , , ( occurrence = 1), , , , .

:

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

strpos(substr(string, strpos(string, substring)), chr(10)) + strpos(string, substring) - 1