将函数从 Oracle 转换为 PostgreSQL

Converting function from Oracle to PostgreSQL

提问by Zachary Burke

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

我正在将一些东西从 Oracle 转换为 PostgreSQL。在 Oracle 文件中有一个函数：

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:

在 PostgreSQL 中这不存在，所以我查找了一个等效的函数。我发现：

position(substring in string)

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

但这不允许起始位置和第 n 个位置参数。

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?

有没有办法让这个函数从给定的点开始？或者是否有更好的函数可以在 PostgreSQL 中使用，我可以在其中指定起始位置和第 n 个位置？

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

这必须适用于 PostgreSQL 8.2.15，因为这是我们在数据库上运行的版本。

回答by klin

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.

strpos(str, sub)Postgres 中的功能相当于instr(str, sub)Oracle 中的功能。不幸的是，该函数没有第三和第四个参数，所以 Postgres 中的表达式肯定更复杂。

The function substr(str, n)gives a substring of strstarting from nposition.

该函数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

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

作为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)- length(sub)- pos- shift+ 3;
    end if;
end $$;

Some checks (Examples from OLAP DML Functions):

一些检查（来自OLAP DML 函数的示例）：

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:

reverse()Postgres 8.2 中没有函数。你可以使用这个：

-- 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 $$;

回答by Patrick

The simplest form:

最简单的形式：

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

With a positionparameter:

带position参数：

For a positive positionvalue:

对于正值position：

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

For a negative positionvalue:

对于负值position：

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

With an occurrenceparameter:

带occurrence参数：

This does not exist in PostgreSQL. You don't seem to need it (example gives occurrence = 1) but if you do then you need to write a function that recursively works on sub-strings extracted from the second version.

这在 PostgreSQL 中不存在。您似乎不需要它（示例给出occurrence = 1），但是如果您这样做了，那么您需要编写一个函数，该函数递归地处理从第二个版本中提取的子字符串。

So:

所以：

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

becomes

变成

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