1
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
5
>Porting from Oracle PL/SQL</TITLE
8
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
10
HREF="mailto:pgsql-docs@postgresql.org"><LINK
12
TITLE="PostgreSQL 9.1beta1 Documentation"
13
HREF="index.html"><LINK
15
TITLE="PL/pgSQL - SQL Procedural Language"
16
HREF="plpgsql.html"><LINK
18
TITLE="Tips for Developing in PL/pgSQL"
19
HREF="plpgsql-development-tips.html"><LINK
21
TITLE="PL/Tcl - Tcl Procedural Language"
22
HREF="pltcl.html"><LINK
25
HREF="stylesheet.css"><META
26
HTTP-EQUIV="Content-Type"
27
CONTENT="text/html; charset=ISO-8859-1"><META
29
CONTENT="2011-04-27T21:20:33"></HEAD
35
SUMMARY="Header navigation table"
47
>PostgreSQL 9.1beta1 Documentation</A
56
TITLE="Tips for Developing in PL/pgSQL"
57
HREF="plpgsql-development-tips.html"
66
TITLE="PL/pgSQL - SQL Procedural Language"
80
> Procedural Language</TD
86
TITLE="PL/pgSQL - SQL Procedural Language"
95
TITLE="PL/Tcl - Tcl Procedural Language"
110
NAME="PLPGSQL-PORTING"
111
>39.12. Porting from <SPAN
117
> This section explains differences between
125
language and Oracle's <SPAN
129
to help developers who port applications from
142
> is similar to PL/SQL in many
143
aspects. It is a block-structured, imperative language, and all
144
variables have to be declared. Assignments, loops, conditionals
145
are similar. The main differences you should keep in mind when
160
> If a name used in a SQL command could be either a column name of a
161
table or a reference to a variable of the function,
165
> treats it as a column name. This corresponds
172
>plpgsql.variable_conflict</TT
177
behavior, which is not the default,
179
HREF="plpgsql-implementation.html#PLPGSQL-VAR-SUBST"
182
It's often best to avoid such ambiguities in the first place,
183
but if you have to port a large amount of code that depends on
184
this behavior, setting <TT
186
>variable_conflict</TT
196
> the function body must be written as
197
a string literal. Therefore you need to use dollar quoting or escape
198
single quotes in the function body. (See <A
199
HREF="plpgsql-development-tips.html#PLPGSQL-QUOTE-TIPS"
206
> Instead of packages, use schemas to organize your functions
212
> Since there are no packages, there are no package-level variables
213
either. This is somewhat annoying. You can keep per-session state
214
in temporary tables instead.
229
> counts down from the second
230
number to the first, while <SPAN
234
from the first number to the second, requiring the loop bounds
235
to be swapped when porting. This incompatibility is unfortunate
236
but is unlikely to be changed. (See <A
237
HREF="plpgsql-control-structures.html#PLPGSQL-INTEGER-FOR"
247
> loops over queries (other than cursors) also work
248
differently: the target variable(s) must have been declared,
252
> always declares them implicitly.
253
An advantage of this is that the variable values are still accessible
254
after the loop exits.
259
> There are various notational differences for the use of cursor
272
>39.12.1. Porting Examples</A
276
HREF="plpgsql-porting.html#PGSQL-PORTING-EX1"
278
> shows how to port a simple
290
NAME="PGSQL-PORTING-EX1"
294
>Example 39-7. Porting a Simple Function from <SPAN
311
CLASS="PROGRAMLISTING"
312
>CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
316
IF v_version IS NULL THEN
319
RETURN v_name || '/' || v_version;
326
> Let's go through this function and see the differences compared to
340
> key word in the function
341
prototype (not the function body) becomes
360
> clause because <SPAN
364
is not the only possible function language.
372
>, the function body is considered
373
to be a string literal, so you need to use quote marks or dollar
374
quotes around it. This substitutes for the terminating <TT
378
in the Oracle approach.
386
> command does not exist in
390
>, and is not needed since errors are
391
reported automatically.
398
> This is how this function would look when ported to
405
CLASS="PROGRAMLISTING"
406
>CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name varchar,
408
RETURNS varchar AS $$
410
IF v_version IS NULL THEN
413
RETURN v_name || '/' || v_version;
415
$$ LANGUAGE plpgsql;</PRE
421
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX2"
423
> shows how to port a
424
function that creates another function and how to handle the
425
ensuing quoting problems.
430
NAME="PLPGSQL-PORTING-EX2"
434
>Example 39-8. Porting a Function that Creates Another Function from <SPAN
443
> The following procedure grabs rows from a
447
> statement and builds a large function
448
with the results in <TT
451
> statements, for the
455
> This is the Oracle version:
457
CLASS="PROGRAMLISTING"
458
>CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
459
CURSOR referrer_keys IS
460
SELECT * FROM cs_referrer_keys
462
func_cmd VARCHAR(4000);
464
func_cmd := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR,
465
v_domain IN VARCHAR, v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';
467
FOR referrer_key IN referrer_keys LOOP
468
func_cmd := func_cmd ||
469
' IF v_' || referrer_key.kind
470
|| ' LIKE ''' || referrer_key.key_string
471
|| ''' THEN RETURN ''' || referrer_key.referrer_type
475
func_cmd := func_cmd || ' RETURN NULL; END;';
477
EXECUTE IMMEDIATE func_cmd;
484
> Here is how this function would end up in <SPAN
489
CLASS="PROGRAMLISTING"
490
>CREATE OR REPLACE FUNCTION cs_update_referrer_type_proc() RETURNS void AS $func$
492
referrer_keys CURSOR IS
493
SELECT * FROM cs_referrer_keys
498
func_body := 'BEGIN';
500
FOR referrer_key IN referrer_keys LOOP
501
func_body := func_body ||
502
' IF v_' || referrer_key.kind
503
|| ' LIKE ' || quote_literal(referrer_key.key_string)
504
|| ' THEN RETURN ' || quote_literal(referrer_key.referrer_type)
508
func_body := func_body || ' RETURN NULL; END;';
511
'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host varchar,
515
|| quote_literal(func_body)
516
|| ' LANGUAGE plpgsql;' ;
520
$func$ LANGUAGE plpgsql;</PRE
522
Notice how the body of the function is built separately and passed
526
> to double any quote marks in it. This
527
technique is needed because we cannot safely use dollar quoting for
528
defining the new function: we do not know for sure what strings will
529
be interpolated from the <TT
531
>referrer_key.key_string</TT
533
(We are assuming here that <TT
535
>referrer_key.kind</TT
537
trusted to always be <TT
549
>referrer_key.key_string</TT
551
anything, in particular it might contain dollar signs.) This function
552
is actually an improvement on the Oracle original, because it will
553
not generate broken code when <TT
555
>referrer_key.key_string</TT
559
>referrer_key.referrer_type</TT
560
> contain quote marks.
565
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX3"
567
> shows how to port a function
571
> parameters and string manipulation.
575
> does not have a built-in
579
> function, but you can create one
580
using a combination of other
582
HREF="plpgsql-porting.html#PLPGSQL-PORTING-APPENDIX"
592
> that you can use to make your porting
598
NAME="PLPGSQL-PORTING-EX3"
602
>Example 39-9. Porting a Procedure With String Manipulation and
606
> Parameters from <SPAN
616
> The following <SPAN
619
> PL/SQL procedure is used
620
to parse a URL and return several elements (host, path, and query).
623
> This is the Oracle version:
625
CLASS="PROGRAMLISTING"
626
>CREATE OR REPLACE PROCEDURE cs_parse_url(
628
v_host OUT VARCHAR, -- This will be passed back
629
v_path OUT VARCHAR, -- This one too
630
v_query OUT VARCHAR) -- And this one
638
a_pos1 := instr(v_url, '//');
643
a_pos2 := instr(v_url, '/', a_pos1 + 2);
645
v_host := substr(v_url, a_pos1 + 2);
650
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
651
a_pos1 := instr(v_url, '?', a_pos2 + 1);
654
v_path := substr(v_url, a_pos2);
658
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
659
v_query := substr(v_url, a_pos1 + 1);
666
> Here is a possible translation into <SPAN
671
CLASS="PROGRAMLISTING"
672
>CREATE OR REPLACE FUNCTION cs_parse_url(
674
v_host OUT VARCHAR, -- This will be passed back
675
v_path OUT VARCHAR, -- This one too
676
v_query OUT VARCHAR) -- And this one
685
a_pos1 := instr(v_url, '//');
690
a_pos2 := instr(v_url, '/', a_pos1 + 2);
692
v_host := substr(v_url, a_pos1 + 2);
697
v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2);
698
a_pos1 := instr(v_url, '?', a_pos2 + 1);
701
v_path := substr(v_url, a_pos2);
705
v_path := substr(v_url, a_pos2, a_pos1 - a_pos2);
706
v_query := substr(v_url, a_pos1 + 1);
708
$$ LANGUAGE plpgsql;</PRE
711
This function could be used like this:
713
CLASS="PROGRAMLISTING"
714
>SELECT * FROM cs_parse_url('http://foobar.com/query.cgi?baz');</PRE
720
HREF="plpgsql-porting.html#PLPGSQL-PORTING-EX4"
722
> shows how to port a procedure
723
that uses numerous features that are specific to Oracle.
728
NAME="PLPGSQL-PORTING-EX4"
732
>Example 39-10. Porting a Procedure from <SPAN
741
> The Oracle version:
744
CLASS="PROGRAMLISTING"
745
>CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
746
a_running_job_count INTEGER;
747
PRAGMA AUTONOMOUS_TRANSACTION;<A
748
NAME="CO.PLPGSQL-PORTING-PRAGMA"
754
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;<A
755
NAME="CO.PLPGSQL-PORTING-LOCKTABLE"
761
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
763
IF a_running_job_count > 0 THEN
764
COMMIT; -- free lock<A
765
NAME="CO.PLPGSQL-PORTING-COMMIT"
770
raise_application_error(-20000,
771
'Unable to create a new job: a job is currently running.');
774
DELETE FROM cs_active_job;
775
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
778
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, sysdate);
780
WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists
789
> Procedures like this can easily be converted into <SPAN
793
functions returning <TT
797
particular is interesting because it can teach us some things:
805
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-PRAGMA"
821
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-LOCKTABLE"
834
the lock will not be released until the calling transaction is
839
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-COMMIT"
845
> You cannot issue <TT
852
> function. The function is
853
running within some outer transaction and so <TT
857
would imply terminating the function's execution. However, in
858
this particular case it is not necessary anyway, because the lock
862
> will be released when
870
> This is how we could port this procedure to <SPAN
876
CLASS="PROGRAMLISTING"
877
>CREATE OR REPLACE FUNCTION cs_create_job(v_job_id integer) RETURNS void AS $$
879
a_running_job_count integer;
881
LOCK TABLE cs_jobs IN EXCLUSIVE MODE;
883
SELECT count(*) INTO a_running_job_count FROM cs_jobs WHERE end_stamp IS NULL;
885
IF a_running_job_count > 0 THEN
886
RAISE EXCEPTION 'Unable to create a new job: a job is currently running';<A
887
NAME="CO.PLPGSQL-PORTING-RAISE"
894
DELETE FROM cs_active_job;
895
INSERT INTO cs_active_job(job_id) VALUES (v_job_id);
898
INSERT INTO cs_jobs (job_id, start_stamp) VALUES (v_job_id, now());
900
WHEN unique_violation THEN <A
901
NAME="CO.PLPGSQL-PORTING-EXCEPTION"
906
-- don't worry if it already exists
909
$$ LANGUAGE plpgsql;</PRE
918
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-RAISE"
927
> is considerably different from
928
Oracle's statement, although the basic case <TT
942
HREF="plpgsql-porting.html#CO.PLPGSQL-PORTING-EXCEPTION"
948
> The exception names supported by <SPAN
952
different from Oracle's. The set of built-in exception names
953
is much larger (see <A
954
HREF="errcodes-appendix.html"
957
is not currently a way to declare user-defined exception names,
958
although you can throw user-chosen SQLSTATE values instead.
964
The main functional difference between this procedure and the
965
Oracle equivalent is that the exclusive lock on the <TT
969
table will be held until the calling transaction completes. Also, if
970
the caller later aborts (for example due to an error), the effects of
971
this procedure will be rolled back.
980
NAME="PLPGSQL-PORTING-OTHER"
981
>39.12.2. Other Things to Watch For</A
984
> This section explains a few other things to watch for when porting
999
NAME="PLPGSQL-PORTING-EXCEPTIONS"
1000
>39.12.2.1. Implicit Rollback after Exceptions</A
1006
>, when an exception is caught by an
1010
> clause, all database changes since the block's
1014
> are automatically rolled back. That is, the behavior
1015
is equivalent to what you'd get in Oracle with:
1018
CLASS="PROGRAMLISTING"
1032
If you are translating an Oracle procedure that uses
1040
your task is easy: just omit the <TT
1047
>. If you have a procedure that uses
1054
> in a different way
1055
then some actual thought will be required.
1077
> works similarly to the
1081
> version, but you have to remember to use
1084
>quote_literal</CODE
1089
> as described in <A
1090
HREF="plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN"
1092
>. Constructs of the
1095
>EXECUTE 'SELECT * FROM $1';</TT
1097
reliably unless you use these functions.
1105
NAME="PLPGSQL-PORTING-OPTIMIZATION"
1106
>39.12.2.3. Optimizing <SPAN
1115
> gives you two function creation
1116
modifiers to optimize execution: <SPAN
1120
the function always returns the same result when given the same
1121
arguments) and <SPAN
1124
> (whether the function
1125
returns null if any argument is null). Consult the <A
1126
HREF="sql-createfunction.html"
1129
reference page for details.
1132
> When making use of these optimization attributes, your
1135
>CREATE FUNCTION</TT
1136
> statement might look something
1140
CLASS="PROGRAMLISTING"
1141
>CREATE FUNCTION foo(...) RETURNS integer AS $$
1143
$$ LANGUAGE plpgsql STRICT IMMUTABLE;</PRE
1153
NAME="PLPGSQL-PORTING-APPENDIX"
1154
>39.12.3. Appendix</A
1157
> This section contains the code for a set of Oracle-compatible
1161
> functions that you can use to simplify
1162
your porting efforts.
1165
CLASS="PROGRAMLISTING"
1167
-- instr functions that mimic Oracle's counterpart
1168
-- Syntax: instr(string1, string2, [n], [m]) where [] denotes optional parameters.
1170
-- Searches string1 beginning at the nth character for the mth occurrence
1171
-- of string2. If n is negative, search backwards. If m is not passed,
1172
-- assume 1 (search starts at first character).
1175
CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
1179
pos:= instr($1, $2, 1);
1182
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
1185
CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
1186
RETURNS integer AS $$
1188
pos integer NOT NULL DEFAULT 0;
1194
IF beg_index > 0 THEN
1195
temp_str := substring(string FROM beg_index);
1196
pos := position(string_to_search IN temp_str);
1201
RETURN pos + beg_index - 1;
1204
ss_length := char_length(string_to_search);
1205
length := char_length(string);
1206
beg := length + beg_index - ss_length + 2;
1208
WHILE beg > 0 LOOP
1209
temp_str := substring(string FROM beg FOR ss_length);
1210
pos := position(string_to_search IN temp_str);
1222
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
1225
CREATE FUNCTION instr(string varchar, string_to_search varchar,
1226
beg_index integer, occur_index integer)
1227
RETURNS integer AS $$
1229
pos integer NOT NULL DEFAULT 0;
1230
occur_number integer NOT NULL DEFAULT 0;
1237
IF beg_index > 0 THEN
1239
temp_str := substring(string FROM beg_index);
1241
FOR i IN 1..occur_index LOOP
1242
pos := position(string_to_search IN temp_str);
1245
beg := beg + pos - 1;
1250
temp_str := substring(string FROM beg + 1);
1259
ss_length := char_length(string_to_search);
1260
length := char_length(string);
1261
beg := length + beg_index - ss_length + 2;
1263
WHILE beg > 0 LOOP
1264
temp_str := substring(string FROM beg FOR ss_length);
1265
pos := position(string_to_search IN temp_str);
1268
occur_number := occur_number + 1;
1270
IF occur_number = occur_index THEN
1281
$$ LANGUAGE plpgsql STRICT IMMUTABLE;</PRE
1289
SUMMARY="Footer navigation table"
1300
HREF="plpgsql-development-tips.html"
1328
>Tips for Developing in <SPAN
1345
>PL/Tcl - Tcl Procedural Language</TD
b'\\ No newline at end of file'