This section explains differences between <productname>PostgreSQL</productname>'s <application>PL/pgSQL</application> language and Oracle's <application>PL/SQL</application> language, to help developers who port applications from <trademark class="registered">Oracle</trademark> to <productname>PostgreSQL</productname>. 本節ではOracle®からPostgreSQLへアプリケーションを移植する開発者の手助けとなるように、PostgreSQLのPL/pgSQL言語とOracleのPL/SQL言語の違いについて説明します。
<application>PL/pgSQL</application> is similar to PL/SQL in many aspects. It is a block-structured, imperative language, and all variables have to be declared. Assignments, loops, and conditionals are similar. The main differences you should keep in mind when porting from <application>PL/SQL</application> to <application>PL/pgSQL</application> are: PL/pgSQLは多くの点でPL/SQLに似ています。 それはブロックで構成されていて、厳格な言語であり、全ての変数は宣言されなければならない点です。 代入やループ、条件分岐も同様です。 PL/SQLからPL/pgSQLに移植する際に注意しなければならない、主な違いを以下に示します。
If a name used in an SQL command could be either a column name of a
table used in the command or a reference to a variable of the function,
<application>PL/SQL</application> treats it as a column name.
By default, <application>PL/pgSQL</application> will throw an error
complaining that the name is ambiguous. You can specify
<literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal>
to change this behavior to match <application>PL/SQL</application>,
as explained in <xref linkend="plpgsql-var-subst"/>.
It's often best to avoid such ambiguities in the first place,
but if you have to port a large amount of code that depends on
this behavior, setting <literal>variable_conflict</literal> may be the
best solution.
SQLコマンド内に使用された名前が、コマンドで使われているテーブルの列名または関数の変数への参照のどちらにもなり得る場合、PL/SQLは列名として処理します。
デフォルトではPL/pgSQLは名前が曖昧であるというエラーを発生します。
41.11.1の説明のようにplpgsql.variable_conflict
= use_column
と指定することで、この振舞いをPL/SQLに合わせることができます。
初期段階において、そのようなあいまいさを避けることが最善です。
しかしこの動作に依存するコードの量が多いものを移植しなければならない場合、variable_conflict
を使用することが最善の解法かもしれません。
In <productname>PostgreSQL</productname> the function body must be written as a string literal. Therefore you need to use dollar quoting or escape single quotes in the function body. (See <xref linkend="plpgsql-quote-tips"/>.) PostgreSQLの関数本体は文字列リテラルとして書かなければなりません。 したがって、関数本体内部でドル引用符を使用するか、単一引用符をエスケープする必要があります。 (41.12.1を参照してください)。
Data type names often need translation. For example, in Oracle string
values are commonly declared as being of type <type>varchar2</type>, which
is a non-SQL-standard type. In <productname>PostgreSQL</productname>,
use type <type>varchar</type> or <type>text</type> instead. Similarly, replace
type <type>number</type> with <type>numeric</type>, or use some other numeric
data type if there's a more appropriate one.
データ型名はしばしば翻訳が必要です。
たとえば、Oracleでは文字列の値はよくvarchar2
型と宣言されますが、それは非標準SQL型です。
PostgreSQLでは、その代わりにvarchar
型またはtext
型を使ってください。
同様に、number
型はnumeric
型で置き換えるか、より適切なものがあるなら他の数値データ型を使ってください。
Instead of packages, use schemas to organize your functions into groups. パッケージの代わりに、スキーマを使用して関数群をグループにまとめてください。
Since there are no packages, there are no package-level variables either. This is somewhat annoying. You can keep per-session state in temporary tables instead. パッケージがないため、パッケージレベルの変数もありません。 これは幾分か厄介なことです。 代わって、セッションごとの状態を一時テーブル内部に保存できます。
Integer <command>FOR</command> loops with <literal>REVERSE</literal> work
differently: <application>PL/SQL</application> counts down from the second
number to the first, while <application>PL/pgSQL</application> counts down
from the first number to the second, requiring the loop bounds
to be swapped when porting. This incompatibility is unfortunate
but is unlikely to be changed. (See <xref
linkend="plpgsql-integer-for"/>.)
REVERSE
を付けた整数FOR
ループの処理は異なります。
PL/SQLでは最後の数から最初の数へ減少しながら処理しますが、PL/pgSQLでは最初の数から最後の数へ減少しながら処理します。
移植において、ループの両端となる最初の数と最後の数を交換する必要があります。
この非互換性は不幸なことですが、変わりそうもありません。
(41.6.5.5を見てください。)
<command>FOR</command> loops over queries (other than cursors) also work
differently: the target variable(s) must have been declared,
whereas <application>PL/SQL</application> always declares them implicitly.
An advantage of this is that the variable values are still accessible
after the loop exits.
問い合わせ上のFOR
ループも(カーソルを除いて)異なって処理されます。
対象の変数は宣言されなければなりませんが、PL/SQLは常にそれらを暗黙的に宣言します。
この優位点は変数値をループを抜けてからでも依然としてアクセスできることです。
There are various notational differences for the use of cursor variables. カーソル変数の使用に対する様々な表記上の違いがあります。
<xref linkend="pgsql-porting-ex1"/> shows how to port a simple function from <application>PL/SQL</application> to <application>PL/pgSQL</application>. 例 41.9に簡単な関数のPL/SQLからPL/pgSQLへの移植方法を示します。
例41.9 簡単な関数のPL/SQLからPL/pgSQLへの移植
Here is an <productname>Oracle</productname> <application>PL/SQL</application> function: 以下はOracle PL/SQLの関数です。
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar2, v_version varchar2) RETURN varchar2 IS BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; / show errors;
Let's go through this function and see the differences compared to <application>PL/pgSQL</application>: この関数を通じて、PL/pgSQLとの違いを見てみましょう。
The type name <type>varchar2</type> has to be changed to <type>varchar</type>
or <type>text</type>. In the examples in this section, we'll
use <type>varchar</type>, but <type>text</type> is often a better choice if
you do not need specific string length limits.
型名varchar2
は、varchar
またはtext
に変えなければなりません。
この節の例ではvarchar
を使いますが、文字列を特定の長さに制限する必要がないのであればtext
の方がたいていは良い選択です。
The <literal>RETURN</literal> key word in the function
prototype (not the function body) becomes
<literal>RETURNS</literal> in
<productname>PostgreSQL</productname>.
Also, <literal>IS</literal> becomes <literal>AS</literal>, and you need to
add a <literal>LANGUAGE</literal> clause because <application>PL/pgSQL</application>
is not the only possible function language.
関数プロトタイプ内のRETURN
キーワード(関数本体ではありません)はPostgreSQLではRETURNS
になります。
同様にIS
はAS
になります。
PL/pgSQL以外の言語でも関数を記述できるため、LANGUAGE
句が必要となります。
In <productname>PostgreSQL</productname>, the function body is considered
to be a string literal, so you need to use quote marks or dollar
quotes around it. This substitutes for the terminating <literal>/</literal>
in the Oracle approach.
PostgreSQLは関数本体を文字列リテラルと考えます。
したがって、それを囲むドル引用符または他の引用符が必要です。
これは/
で終了するOracleの方法の代替です。
The <literal>show errors</literal> command does not exist in
<productname>PostgreSQL</productname>, and is not needed since errors are
reported automatically.
PostgreSQLにはshow errors
コマンドはありません。
また、エラーが自動的に表示されるため、必要ありません。
This is how this function would look when ported to <productname>PostgreSQL</productname>: それではPostgreSQLに移植されると、この関数がどのようになるか見てみましょう。
CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar, v_version varchar) RETURNS varchar AS $$ BEGIN IF v_version IS NULL THEN RETURN v_name; END IF; RETURN v_name || '/' || v_version; END; $$ LANGUAGE plpgsql;
<xref linkend="plpgsql-porting-ex2"/> shows how to port a function that creates another function and how to handle the ensuing quoting problems. 例 41.10は、他の関数を生成する関数を移植する方法、ならびに、その結果発生する引用符問題を扱う方法を示します。
例41.10 他の関数を生成するPL/SQLをPL/pgSQLに移植
The following procedure grabs rows from a
<command>SELECT</command> statement and builds a large function
with the results in <literal>IF</literal> statements, for the
sake of efficiency.
以下の手続きは、SELECT
文からの行を取って、効率のためにIF
文で結果を巨大な関数に埋め込んでいます。
This is the Oracle version: 以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS CURSOR referrer_keys IS SELECT * FROM cs_referrer_keys ORDER BY try_order; func_cmd VARCHAR(4000); BEGIN func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR2, v_domain IN VARCHAR2, v_url IN VARCHAR2) RETURN VARCHAR2 IS BEGIN'; FOR referrer_key IN referrer_keys LOOP func_cmd := func_cmd || ' IF v_' || referrer_key.kind || ' LIKE ''' || referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type || '''; END IF;'; END LOOP; func_cmd := func_cmd || ' RETURN NULL; END;'; EXECUTE IMMEDIATE func_cmd; END; / show errors;
Here is how this function would end up in <productname>PostgreSQL</productname>: この関数をPostgreSQLで記述するとこうなるでしょう。
CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc() AS $func$ DECLARE referrer_keys CURSOR IS SELECT * FROM cs_referrer_keys ORDER BY try_order; func_body text; func_cmd text; BEGIN func_body := 'BEGIN'; FOR referrer_key IN referrer_keys LOOP func_body := func_body || ' IF v_' || referrer_key.kind || ' LIKE ' || quote_literal(referrer_key.key_string) || ' THEN RETURN ' || quote_literal(referrer_key.referrer_type) || '; END IF;' ; END LOOP; func_body := func_body || ' RETURN NULL; END;'; func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar, v_domain varchar, v_url varchar) RETURNS varchar AS ' || quote_literal(func_body) || ' LANGUAGE plpgsql;' ; EXECUTE func_cmd; END; $func$ LANGUAGE plpgsql;
Notice how the body of the function is built separately and passed
through <literal>quote_literal</literal> to double any quote marks in it. This
technique is needed because we cannot safely use dollar quoting for
defining the new function: we do not know for sure what strings will
be interpolated from the <structfield>referrer_key.key_string</structfield> field.
(We are assuming here that <structfield>referrer_key.kind</structfield> can be
trusted to always be <literal>host</literal>, <literal>domain</literal>, or
<literal>url</literal>, but <structfield>referrer_key.key_string</structfield> might be
anything, in particular it might contain dollar signs.) This function
is actually an improvement on the Oracle original, because it will
not generate broken code when <structfield>referrer_key.key_string</structfield> or
<structfield>referrer_key.referrer_type</structfield> contain quote marks.
関数本体を別途作成し、それをquote_literal
に渡して本体内の引用符を二重化する方法に注目してください。
新規の関数を定義する時ドル引用符の使用が安全とは限らないため、この方法が必要となります。
それはreferrer_key.key_string
の領域に、どのような文字列が書き込まれているか不明だからです。
(referrer_key.kind
は常に信用できるhost
かdomain
かurl
であると仮定しますが、どんなものでもreferrer_key.key_string
になり得るので、ドル記号を含む可能性があります。)
この関数はOracle版より実際に改善されています。
それはreferrer_key.key_string
またはreferrer_key.referrer_type
が引用符を含む時、おかしなコードを生成しないからです。
<xref linkend="plpgsql-porting-ex3"/> shows how to port a function
with <literal>OUT</literal> parameters and string manipulation.
<productname>PostgreSQL</productname> does not have a built-in
<function>instr</function> function, but you can create one
using a combination of other
functions. In <xref linkend="plpgsql-porting-appendix"/> there is a
<application>PL/pgSQL</application> implementation of
<function>instr</function> that you can use to make your porting
easier.
例 41.11は、OUT
パラメータを持ち、文字列操作を行う関数の移植方法を示します。
PostgreSQLには組み込みのinstr
関数はありませんが、他の関数を組み合わせることで作成できます。
41.13.3に、移植を簡略化できるようにinstr
のPL/pgSQLによる実装を示します。
例41.11 文字列操作を行い、OUT
パラメータを持つPL/SQLプロシージャのPL/pgSQLへの移植
The following <productname>Oracle</productname> PL/SQL procedure is used to parse a URL and return several elements (host, path, and query). 以下のOracle PL/SQLプロシージャは、URLを解析していくつかの要素(ホスト、パス、問い合わせ)を返します。
This is the Oracle version: 以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_parse_url(
v_url IN VARCHAR2,
v_host OUT VARCHAR2, -- This will be passed back
v_path OUT VARCHAR2, -- This one too
v_query OUT VARCHAR2) -- And this one
v_host OUT VARCHAR2, -- この値は戻されます
v_path OUT VARCHAR2, -- この値も戻されます
v_query OUT VARCHAR2) -- この値も戻されます
IS
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := '/';
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
/
show errors;
Here is a possible translation into <application>PL/pgSQL</application>: PL/pgSQLへの可能な変換は以下のようになります。
CREATE OR REPLACE FUNCTION cs_parse_url(
v_url IN VARCHAR,
v_host OUT VARCHAR, -- This will be passed back
v_path OUT VARCHAR, -- This one too
v_query OUT VARCHAR) -- And this one
v_host OUT VARCHAR, -- この値は戻されます
v_path OUT VARCHAR, -- この値も戻されます
v_query OUT VARCHAR) -- この値も戻されます
AS $$
DECLARE
a_pos1 INTEGER;
a_pos2 INTEGER;
BEGIN
v_host := NULL;
v_path := NULL;
v_query := NULL;
a_pos1 := instr(v_url, '//');
IF a_pos1 = 0 THEN
RETURN;
END IF;
a_pos2 := instr(v_url, '/', a_pos1 + 2);
IF a_pos2 = 0 THEN
v_host := substr(v_url, a_pos1 + 2);
v_path := '/';
RETURN;
END IF;
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
a_pos1 := instr(v_url, '?', a_pos2 + 1);
IF a_pos1 = 0 THEN
v_path := substr(v_url, a_pos2);
RETURN;
END IF;
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
v_query := substr(v_url, a_pos1 + 1);
END;
$$ LANGUAGE plpgsql;
This function could be used like this: この関数は以下のように使用できます。
SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');
<xref linkend="plpgsql-porting-ex4"/> shows how to port a procedure that uses numerous features that are specific to Oracle. 例 41.12は、Oracleに特化した多くの機能を使用したプロシージャの移植方法を示します。
例41.12 PL/SQLプロシージャのPL/pgSQLへの移植
The Oracle version: 以下はOracle版です。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS a_running_job_count INTEGER; BEGIN LOCK TABLE cs_jobs IN EXCLUSIVE MODE; SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL; IF a_running_job_count > 0 THEN COMMIT; -- free lock COMMIT; -- ロックを解放 raise_application_error(-20000, 'Unable to create a new job: a job is currently running.'); END IF; DELETE FROM cs_active_job; INSERT INTO cs_active_job(job_id) VALUES (v_job_id); BEGIN INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now()); EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists WHEN dup_val_on_index THEN NULL; -- 既存であっても問題なし END; COMMIT; END; / show errors
This is how we could port this procedure to <application>PL/pgSQL</application>: それでは、このプロシージャをPL/pgSQLに移植することができた方法を見てみましょう。
CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id integer) AS $$ DECLARE a_running_job_count integer; BEGIN LOCK TABLE cs_jobs IN EXCLUSIVE MODE; SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL; IF a_running_job_count > 0 THEN COMMIT; -- free lock COMMIT; -- ロックを解放 RAISE EXCEPTION 'Unable to create a new job: a job is currently running'; -- (1) END IF; DELETE FROM cs_active_job; INSERT INTO cs_active_job(job_id) VALUES (v_job_id); BEGIN INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now()); EXCEPTION WHEN unique_violation THEN -- (2) -- don't worry if it already exists -- 既存であっても問題なし END; COMMIT; END; $$ LANGUAGE plpgsql;
The syntax of <literal>RAISE</literal> is considerably different from
Oracle's statement, although the basic case <literal>RAISE</literal>
<replaceable class="parameter">exception_name</replaceable> works
similarly.
基本の | |
The exception names supported by <application>PL/pgSQL</application> are different from Oracle's. The set of built-in exception names is much larger (see <xref linkend="errcodes-appendix"/>). There is not currently a way to declare user-defined exception names, although you can throw user-chosen SQLSTATE values instead. PL/pgSQLがサポートする例外の名称は、Oracleと異なります。 提供する例外の名称は、はるかに広範囲です(付録Aを参照してください)。 今のところ、ユーザ定義の例外名称を宣言できません。 しかし代わりにユーザが選択したSQLSTATE値を返すことができます。 |
This section explains a few other things to watch for when porting Oracle <application>PL/SQL</application> functions to <productname>PostgreSQL</productname>. 本節では、Oracle PL/SQL関数のPostgreSQLへの移植における、その他の注意事項を説明します。
In <application>PL/pgSQL</application>, when an exception is caught by an
<literal>EXCEPTION</literal> clause, all database changes since the block's
<literal>BEGIN</literal> are automatically rolled back. That is, the behavior
is equivalent to what you'd get in Oracle with:
PL/pgSQLにおいてEXCEPTION
句が例外を捕捉すると、BEGIN
以降のそのブロックにおけるデータベースの変更が自動的にロールバックされます。
すなわち、Oracleで以下のプログラムと同等の処理が実行されます。
BEGIN SAVEPOINT s1; ... code here ... EXCEPTION WHEN ... THEN ROLLBACK TO s1; ... code here ... WHEN ... THEN ROLLBACK TO s1; ... code here ... END;
If you are translating an Oracle procedure that uses
<command>SAVEPOINT</command> and <command>ROLLBACK TO</command> in this style,
your task is easy: just omit the <command>SAVEPOINT</command> and
<command>ROLLBACK TO</command>. If you have a procedure that uses
<command>SAVEPOINT</command> and <command>ROLLBACK TO</command> in a different way
then some actual thought will be required.
このような方式でSAVEPOINT
とROLLBACK TO
を使用したOracleのプロシージャの書き換えは簡単です。
単にSAVEPOINT
とROLLBACK TO
の処理を削除すればよいだけです。
これと異なった方式でSAVEPOINT
とROLLBACK TO
を使用したプロシージャの時は、それに応じた工夫が必要になると思われます。
EXECUTE
#
The <application>PL/pgSQL</application> version of
<command>EXECUTE</command> works similarly to the
<application>PL/SQL</application> version, but you have to remember to use
<function>quote_literal</function> and
<function>quote_ident</function> as described in <xref
linkend="plpgsql-statements-executing-dyn"/>. Constructs of the
type <literal>EXECUTE 'SELECT * FROM $1';</literal> will not work
reliably unless you use these functions.
PL/pgSQLのEXECUTE
はPL/SQL版とよく似ています。
しかし41.5.4で説明されているquote_literal
とquote_ident
を使うことを覚えておかなければいけません。
これらの関数を使用しない限りEXECUTE ''SELECT * from $1'';
という構文の動作には、信頼性がありません。
<productname>PostgreSQL</productname> gives you two function creation modifiers to optimize execution: <quote>volatility</quote> (whether the function always returns the same result when given the same arguments) and <quote>strictness</quote> (whether the function returns null if any argument is null). Consult the <xref linkend="sql-createfunction"/> reference page for details. PostgreSQLには実行を最適化するために2つの関数生成修飾子があります。 「揮発性」(同じ引数が与えられた場合常に同じ結果を返すかどうか)と「厳密性」(引数のいずれかにNULLが含まれる場合NULLを返します)です。 詳細はCREATE FUNCTIONを参照してください。
When making use of these optimization attributes, your
<command>CREATE FUNCTION</command> statement might look something
like this:
これらの最適化属性を利用するためには、CREATE FUNCTION
文を以下のようにします。
CREATE FUNCTION foo(...) RETURNS integer AS $$ ... $$ LANGUAGE plpgsql STRICT IMMUTABLE;
This section contains the code for a set of Oracle-compatible
<function>instr</function> functions that you can use to simplify
your porting efforts.
本節には、移植作業を簡略化するために使用できる、Oracle互換のinstr
関数のコードがあります。
-- -- instr functions that mimic Oracle's counterpart -- Syntax: instr(string1, string2 [, n [, m]]) -- where [] denotes optional parameters. -- Oracleのものと同じ動作をするinstr関数 -- 構文: instr(string1, string2 [, n [, m]]) -- ただし、[]は省略可能なパラメータ -- -- Search string1, beginning at the nth character, for the mth occurrence -- of string2. If n is negative, search backwards, starting at the abs(n)'th -- character from the end of string1. -- If n is not passed, assume 1 (search starts at first character). -- If m is not passed, assume 1 (find first occurrence). -- Returns starting index of string2 in string1, or 0 if string2 is not found. -- string1内のn番目の文字から始めて、m番目のstring2を探します。 -- nが負の場合、string1の終わりからabs(n)番目の文字から始めて、逆方向に検索します。 -- nが渡されなかった場合は、1とみなします(最初の文字から探し始めます)。 -- mが渡されなかった場合は、1とみなします(最初に一致するものを見つけます)。 -- string1内のstring2の開始位置を、string2が見つからなければ0を返します。 -- CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$ BEGIN RETURN instr($1, $2, 1); END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search_for varchar, beg_index integer) RETURNS integer AS $$ DECLARE pos integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; length integer; ss_length integer; BEGIN IF beg_index > 0 THEN temp_str := substring(string FROM beg_index); pos := position(string_to_search_for IN temp_str); IF pos = 0 THEN RETURN 0; ELSE RETURN pos + beg_index - 1; END IF; ELSIF beg_index < 0 THEN ss_length := char_length(string_to_search_for); length := char_length(string); beg := length + 1 + beg_index; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); IF string_to_search_for = temp_str THEN RETURN beg; END IF; beg := beg - 1; END LOOP; RETURN 0; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE; CREATE FUNCTION instr(string varchar, string_to_search_for varchar, beg_index integer, occur_index integer) RETURNS integer AS $$ DECLARE pos integer NOT NULL DEFAULT 0; occur_number integer NOT NULL DEFAULT 0; temp_str varchar; beg integer; i integer; length integer; ss_length integer; BEGIN IF occur_index <= 0 THEN RAISE 'argument ''%'' is out of range', occur_index USING ERRCODE = '22003'; END IF; IF beg_index > 0 THEN beg := beg_index - 1; FOR i IN 1..occur_index LOOP temp_str := substring(string FROM beg + 1); pos := position(string_to_search_for IN temp_str); IF pos = 0 THEN RETURN 0; END IF; beg := beg + pos; END LOOP; RETURN beg; ELSIF beg_index < 0 THEN ss_length := char_length(string_to_search_for); length := char_length(string); beg := length + 1 + beg_index; WHILE beg > 0 LOOP temp_str := substring(string FROM beg FOR ss_length); IF string_to_search_for = temp_str THEN occur_number := occur_number + 1; IF occur_number = occur_index THEN RETURN beg; END IF; END IF; beg := beg - 1; END LOOP; RETURN 0; ELSE RETURN 0; END IF; END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;