将函数从Oracle转换为PostgreSQL [英] Converting function from Oracle to PostgreSQL

问题描述

我正在将某些内容从Oracle转换为PostgreSQL.在Oracle文件中有一个功能:

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)

或保存在我的文件中

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

在PostgreSQL中不存在，因此我查找了一个等效函数.我发现:

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

position(substring in string)

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

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

是否有使此功能在给定点启动的方法?还是在PostgreSQL中可以使用我可以指定起始位置和第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 8.2.15上工作，因为那是我们在数据库上运行的版本.

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

推荐答案

Postgres中的函数strpos(str, sub)与Oracle中的instr(str, sub)等效.不幸的是，该函数没有第三个和第四个参数，因此Postgres中的表达式必须更复杂.

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.

函数substr(str, n)从n位置开始给出str的子字符串.

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

由于instr()是一个强大的功能，我根据自己的需要在plpgsql中编写了它.

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

一些检查(例如 OLAP DML函数):

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

Postgres 8.2中没有reverse()功能.您可以使用此:

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

这篇关于将函数从Oracle转换为PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！