Porting from Oracle PL/SQL

PostgreSQL 7.3.4 Documentation
Prev | Chapter 19. PL/pgSQL - SQL Procedural Language | Next

# 19.11. Porting from Oracle PL/SQL

> Author: Roberto Mello (`<rmello@fslc.usu.edu>`)

This section explains differences between Oracle's PL/SQL and PostgreSQL's PL/pgSQL languages in the hopes of helping developers port applications from Oracle to PostgreSQL. Most of the code here is from the ArsDigita Clickstream module that I ported to PostgreSQL when I took an internship with OpenForce Inc. in the Summer of 2000.

PL/pgSQL is similar to PL/SQL in many aspects. It is a block structured, imperative language (all variables have to be declared). PL/SQL has many more features than its PostgreSQL counterpart, but PL/pgSQL allows for a great deal of functionality and it is being improved constantly.

## 19.11.1. Main Differences

Some things you should keep in mind when porting from Oracle to PostgreSQL:

- No default parameters in PostgreSQL.

- You can overload functions in PostgreSQL. This is often used to work around the lack of default parameters.

- Assignments, loops and conditionals are similar.

- No need for cursors in PostgreSQL, just put the query in the FOR statement (see example below)

- In PostgreSQL you need to escape single quotes. See Section 19.11.1.1.

### 19.11.1.1. Quote Me on That: Escaping Single Quotes

In PostgreSQL you need to escape single quotes inside your function definition. This can lead to quite amusing code at times, especially if you are creating a function that generates other function(s), as in Example 19-6. One thing to keep in mind when escaping lots of single quotes is that, except for the beginning/ending quotes, all the others will come in even quantity.

Table 19-1 gives the scoop. (You'll love this little chart.)

Table 19-1. Single Quotes Escaping Chart

No. of Quotes | Usage | Example | Result
--- | --- | --- | ---
1 | To begin/terminate function bodies |
    CREATE FUNCTION foo() RETURNS INTEGER AS '...'
    LANGUAGE 'plpgsql';
| as is
2 | In assignments, SELECT statements, to delimit strings, etc. |
    a_output := ''Blah'';
    SELECT * FROM users WHERE f_name=''foobar'';
| SELECT * FROM users WHERE f_name='foobar';
4 | When you need two single quotes in your resulting string without terminating that string. |
    a_output := a_output || '' AND name
        LIKE ''''foobar'''' AND ...''
| AND name LIKE 'foobar' AND ...
6 | When you want double quotes in your resulting string and terminate that string. |
    a_output := a_output || '' AND name
        LIKE ''''foobar''''''
| AND name LIKE 'foobar'
10 | When you want two single quotes in the resulting string (which accounts for 8 quotes) and terminate that string (2 more). You will probably only need that if you were using a function to generate other functions (like in Example 19-6). |
    a_output := a_output || '' if v_'' ||
        referrer_keys.kind || '' like ''''''''''
        || referrer_keys.key_string || ''''''''''
        then return ''''''  || referrer_keys.referrer_type
        || ''''''; end if;'';
| if v_<...> like ''<...>'' then return ''<...>''; end if;

## 19.11.2. Porting Functions

Example 19-5. A Simple Function

Here is an Oracle function:

    CREATE OR REPLACE FUNCTION cs_fmt_browser_version(v_name IN varchar, v_version IN varchar)
    RETURN varchar 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 to PL/pgSQL:

- PostgreSQL does not have named parameters. You have to explicitly alias them inside your function.

- Oracle can have IN, OUT, and INOUT parameters passed to functions. The INOUT, for example, means that the parameter will receive a value and return another. PostgreSQL only has "IN" parameters and functions can return only a single value.

- The RETURN key word in the function prototype (not the function body) becomes RETURNS in PostgreSQL.

- On PostgreSQL functions are created using single quotes as delimiters, so you have to escape single quotes inside your functions (which can be quite annoying at times; see Section 19.11.1.1).

- The /show errors command does not exist in PostgreSQL.

So let's see how this function would look when ported to PostgreSQL:

    CREATE OR REPLACE FUNCTION cs_fmt_browser_version(VARCHAR, VARCHAR)
    RETURNS VARCHAR AS '
    DECLARE
        v_name ALIAS FOR $1;
        v_version ALIAS FOR $2;
    BEGIN
        IF v_version IS NULL THEN
            return v_name;
        END IF;
        RETURN v_name || ''/'' || v_version;
    END;
    ' LANGUAGE 'plpgsql';

Example 19-6. A Function that Creates Another Function

The following procedure grabs rows from a `SELECT` statement and builds a large function with the results in IF statements, for the sake of efficiency. Notice particularly the differences in cursors, FOR loops, and the need to escape single quotes in PostgreSQL.

    CREATE OR REPLACE PROCEDURE cs_update_referrer_type_proc IS
        CURSOR referrer_keys IS
            SELECT * FROM cs_referrer_keys
            ORDER BY try_order;

        a_output VARCHAR(4000);
    BEGIN
        a_output := 'CREATE OR REPLACE FUNCTION cs_find_referrer_type(v_host IN VARCHAR, v_domain IN VARCHAR,
    v_url IN VARCHAR) RETURN VARCHAR IS BEGIN';

        FOR referrer_key IN referrer_keys LOOP
            a_output := a_output || ' IF v_' || referrer_key.kind || ' LIKE ''' ||
    referrer_key.key_string || ''' THEN RETURN ''' || referrer_key.referrer_type ||
    '''; END IF;';
        END LOOP;

        a_output := a_output || ' RETURN NULL; END;';
        EXECUTE IMMEDIATE a_output;
    END;
    /
    show errors

Here is how this function would end up in PostgreSQL:

    CREATE FUNCTION cs_update_referrer_type_proc() RETURNS INTEGER AS '
    DECLARE
        referrer_keys RECORD;  -- Declare a generic record to be used in a FOR
        a_output varchar(4000);
    BEGIN
        a_output := ''CREATE FUNCTION cs_find_referrer_type(VARCHAR,VARCHAR,VARCHAR)
                      RETURNS VARCHAR AS ''''
                         DECLARE
                             v_host ALIAS FOR $1;
                             v_domain ALIAS FOR $2;
                             v_url ALIAS FOR $3;
                         BEGIN '';

        --
        -- Notice how we scan through the results of a query in a FOR loop
        -- using the FOR <record> construct.
        --

        FOR referrer_keys IN SELECT * FROM cs_referrer_keys ORDER BY try_order LOOP
            a_output := a_output || '' IF v_'' || referrer_keys.kind || '' LIKE ''''''''''
                     || referrer_keys.key_string || '''''''''' THEN RETURN ''''''
                     || referrer_keys.referrer_type || ''''''; END IF;'';
        END LOOP;

        a_output := a_output || '' RETURN NULL; END; '''' LANGUAGE ''''plpgsql'''';'';

        -- This works because we are not substituting any variables
        -- Otherwise it would fail. Look at PERFORM for another way to run functions

        EXECUTE a_output;
    END;
    ' LANGUAGE 'plpgsql';

Example 19-7. A Procedure with a lot of String Manipulation and OUT Parameters

The following Oracle PL/SQL procedure is used to parse a URL and return several elements (host, path and query). It is an procedure because in PL/pgSQL functions only one value can be returned (see Section 19.11.3). In PostgreSQL, one way to work around this is to split the procedure in three different functions: one to return the host, another for the path and another for the query.

    CREATE OR REPLACE PROCEDURE 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
    is
        a_pos1 INTEGER;
        a_pos2 INTEGER;
    begin
        v_host := NULL;
        v_path := NULL;
        v_query := NULL;
        a_pos1 := instr(v_url, '//'); -- PostgreSQL doesn't have an instr function

        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 how this procedure could be translated for PostgreSQL:

    CREATE OR REPLACE FUNCTION cs_parse_url_host(VARCHAR) RETURNS VARCHAR AS '
    DECLARE
        v_url ALIAS FOR $1;
        v_host VARCHAR;
        v_path VARCHAR;
        a_pos1 INTEGER;
        a_pos2 INTEGER;
        a_pos3 INTEGER;
    BEGIN
        v_host := NULL;
        a_pos1 := instr(v_url,''//'');

        IF a_pos1 = 0 THEN
            RETURN '''';  -- Return a blank
        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 v_host;
        END IF;

        v_host := substr(v_url, a_pos1 + 2, a_pos2 - a_pos1 - 2 );
        RETURN v_host;
    END;
    ' LANGUAGE 'plpgsql';

> Note: PostgreSQL does not have an `instr` function, so you can work around it using a combination of other functions. I got tired of doing this and created my own `instr` functions that behave exactly like Oracle's (it makes life easier). See the Section 19.11.6 for the code.

## 19.11.3. Procedures

Oracle procedures give a little more flexibility to the developer because nothing needs to be explicitly returned, but it can be through the use of INOUT or OUT parameters.

An example:

    CREATE OR REPLACE PROCEDURE cs_create_job(v_job_id IN INTEGER) IS
        a_running_job_count INTEGER;
        PRAGMA AUTONOMOUS_TRANSACTION;(1)
    BEGIN
        LOCK TABLE cs_jobs IN EXCLUSIVE MODE;(2)

        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(3)
            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, sysdate);
            EXCEPTION WHEN dup_val_on_index THEN NULL; -- don't worry if it already exists(4)
        END;
        COMMIT;
    END;
    /
    show errors

Procedures like this can be easily converted into PostgreSQL functions returning an `INTEGER`. This procedure in particular is interesting because it can teach us some things:

(1)
  There is no pragma statement in PostgreSQL.
(2)
  If you do a `LOCK TABLE` in PL/pgSQL, the lock will not be released until the calling transaction is finished.
(3)
  You also cannot have transactions in PL/pgSQL procedures. The entire function (and other functions called from therein) is executed in a transaction and PostgreSQL rolls back the results if something goes wrong. Therefore only one `BEGIN` statement is allowed.
(4)
  The exception when would have to be replaced by an IF statement.

So let's see one of the ways we could port this procedure to PL/pgSQL:

    CREATE OR REPLACE FUNCTION cs_create_job(INTEGER) RETURNS INTEGER AS '
    DECLARE
        v_job_id ALIAS FOR $1;
        a_running_job_count INTEGER;
        a_num INTEGER;
        -- PRAGMA AUTONOMOUS_TRANSACTION;
    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
            RAISE EXCEPTION ''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);

        SELECT count(*) into a_num
        FROM cs_jobs
        WHERE job_id=v_job_id;
        IF NOT FOUND THEN  -- If nothing was returned in the last query
            -- This job is not in the table so lets insert it.
            INSERT INTO cs_jobs(job_id, start_stamp) VALUES (v_job_id, sysdate());
            RETURN 1;
        ELSE
            RAISE NOTICE ''Job already running.'';(1)
        END IF;

        RETURN 0;
    END;
    ' LANGUAGE 'plpgsql';

(1)
  Notice how you can raise notices (or errors) in PL/pgSQL.

## 19.11.4. Packages

> Note: I haven't done much with packages myself, so if there are mistakes here, please let me know.

Packages are a way Oracle gives you to encapsulate PL/SQL statements and functions into one entity, like Java classes, where you define methods and objects. You can access these objects/methods with a "." (dot). Here is an example of an Oracle package from ACS 4 (the ArsDigita Community System):

    CREATE OR REPLACE PACKAGE BODY acs
    AS
      FUNCTION add_user (
        user_id     IN users.user_id%TYPE DEFAULT NULL,
        object_type     IN acs_objects.object_type%TYPE DEFAULT 'user',
        creation_date   IN acs_objects.creation_date%TYPE DEFAULT sysdate,
        creation_user   IN acs_objects.creation_user%TYPE DEFAULT NULL,
        creation_ip     IN acs_objects.creation_ip%TYPE DEFAULT NULL,
      ...
      ) RETURN users.user_id%TYPE
      IS
        v_user_id       users.user_id%TYPE;
        v_rel_id        membership_rels.rel_id%TYPE;
      BEGIN
        v_user_id := acs_user.new (user_id, object_type, creation_date,
                    creation_user, creation_ip, email, ...
        RETURN v_user_id;
      END;
    END acs;
    /
    show errors

We port this to PostgreSQL by creating the different objects of the Oracle package as functions with a standard naming convention. We have to pay attention to some other details, like the lack of default parameters in PostgreSQL functions. The above package would become something like this:

    CREATE FUNCTION acs__add_user(INTEGER,INTEGER,VARCHAR,TIMESTAMP,INTEGER,INTEGER,...)
    RETURNS INTEGER AS '
    DECLARE
        user_id ALIAS FOR $1;
        object_type ALIAS FOR $2;
        creation_date ALIAS FOR $3;
        creation_user ALIAS FOR $4;
        creation_ip ALIAS FOR $5;
        ...
        v_user_id users.user_id%TYPE;
        v_rel_id membership_rels.rel_id%TYPE;
    BEGIN
        v_user_id := acs_user__new(user_id,object_type,creation_date,creation_user,creation_ip, ...);
        ...

        RETURN v_user_id;
    END;
    ' LANGUAGE 'plpgsql';

## 19.11.5. Other Things to Watch For

### 19.11.5.1. EXECUTE

The PostgreSQL version of `EXECUTE` works nicely, but you have to remember to use `quote_literal(TEXT)` and `quote_string(TEXT)` as described in Section 19.5.4. Constructs of the type EXECUTE ''SELECT * from $1''; will not work unless you use these functions.

### 19.11.5.2. Optimizing PL/pgSQL Functions

PostgreSQL gives you two function creation modifiers to optimize execution: iscachable (function always returns the same result when given the same arguments) and isstrict (function returns NULL if any argument is NULL). Consult the `CREATE FUNCTION` reference for details.

To make use of these optimization attributes, you have to use the WITH modifier in your `CREATE FUNCTION` statement. Something like:

    CREATE FUNCTION foo(...) RETURNS INTEGER AS '
    ...
    ' LANGUAGE 'plpgsql'
    WITH (isstrict, iscachable);

## 19.11.6. Appendix

### 19.11.6.1. Code for my `instr` functions

    --
    -- instr functions that mimic Oracle's counterpart
    -- Syntax: instr(string1,string2,[n],[m]) where [] denotes optional params.
    --
    -- Searches string1 beginning at the nth character for the mth
    -- occurrence of string2. If n is negative, search backwards. If m is
    -- not passed, assume 1 (search starts at first character).
    --
    -- by Roberto Mello (rmello@fslc.usu.edu)
    -- modified by Robert Gaszewski (graszew@poland.com)
    -- Licensed under the GPL v2 or later.
    --

    CREATE FUNCTION instr(VARCHAR,VARCHAR) RETURNS INTEGER AS '
    DECLARE
        pos integer;
    BEGIN
        pos:= instr($1,$2,1);
        RETURN pos;
    END;
    ' LANGUAGE 'plpgsql';

    CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER) RETURNS INTEGER AS '
    DECLARE
        string ALIAS FOR $1;
        string_to_search ALIAS FOR $2;
        beg_index ALIAS FOR $3;
        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 IN temp_str);

           IF pos = 0 THEN
    	         RETURN 0;
    	     ELSE
    	         RETURN pos + beg_index - 1;
    	     END IF;
        ELSE
           ss_length := char_length(string_to_search);
           length := char_length(string);
           beg := length + beg_index - ss_length + 2;

           WHILE beg > 0 LOOP
               temp_str := substring(string FROM beg FOR ss_length);
    	         pos := position(string_to_search IN temp_str);

    	         IF pos > 0 THEN
    		           RETURN beg;
    	         END IF;

    	         beg := beg - 1;
           END LOOP;
           RETURN 0;
        END IF;
    END;
    ' LANGUAGE 'plpgsql';

    --
    -- Written by Robert Gaszewski (graszew@poland.com)
    -- Licensed under the GPL v2 or later.
    --
    CREATE FUNCTION instr(VARCHAR,VARCHAR,INTEGER,INTEGER) RETURNS INTEGER AS '
    DECLARE
        string ALIAS FOR $1;
        string_to_search ALIAS FOR $2;
        beg_index ALIAS FOR $3;
        occur_index ALIAS FOR $4;
        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 beg_index > 0 THEN
            beg := beg_index;
            temp_str := substring(string FROM beg_index);

            FOR i IN 1..occur_index LOOP
                pos := position(string_to_search IN temp_str);

                IF i = 1 THEN
                    beg := beg + pos - 1;
                ELSE
                    beg := beg + pos;
                END IF;

                temp_str := substring(string FROM beg + 1);
            END LOOP;

            IF pos = 0 THEN
                RETURN 0;
            ELSE
                RETURN beg;
            END IF;
        ELSE
            ss_length := char_length(string_to_search);
            length := char_length(string);
            beg := length + beg_index - ss_length + 2;

            WHILE beg > 0 LOOP
                temp_str := substring(string FROM beg FOR ss_length);
                pos := position(string_to_search IN temp_str);

                IF pos > 0 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;
        END IF;
    END;
    ' LANGUAGE 'plpgsql';

Prev | Home | Next
Examples | Up | PL/Tcl - Tcl Procedural Language