Home > Documentation > Oracle to PostgreSQL

This whitepaper explores differences between procedure languages of Oracle and PostgreSQL. Although these languages are basically similar, there are few important differences to remember during migration from one DBMS to another:

1. If there are variable and column with the same name used in a SQL query, Oracle treats it as a column name and PostgreSQL - as a variable name. The good practice of database development is to avoid such ambiguities.
2. While Oracle uses packages to organize functions into semantic groups, PostgreSQL uses schema for the same purpose. Also, there is no package-level variables in PostgreSQL, however it can be emulated as data of temporary service table.
3. PostgreSQL considers the function body as a string, so it is necessary to enclose it in dollar quotes.
4. PostgreSQL requires 'LANGUAGE' specification at the end of function since PL/PgSQL is not the only possible language there.
5. All local variables must be declared using 'DECLARE' keyword right before 'BEGIN'
6. Unlike Oracle, PostgreSQL does not allow 'COMMIT' statement inside function.
7. Convert SQL Function from Oracle to PostgreSQL according to the rules described on this article.

Let us illustrate porting functions from Oracle to PostgreSQL on few examples, starting from simple function concatenating two strings. In Oracle:

CREATE OR REPLACE FUNCTION my_concat(str1 varchar, str2 varchar)
RETURN varchar IS
BEGIN
    IF str2 IS NULL THEN
        RETURN str1;
    END IF;
    RETURN str1 || ' ' || str2;
END;
/
show errors;

And the PostgreSQL equivalent is:

CREATE OR REPLACE FUNCTION my_concat(str1 varchar, str2 varchar)
RETURNS varchar AS $$
BEGIN
    IF str2 IS NULL THEN
        RETURN str1;
    END IF;
    RETURN str1 || ' ' || str2;
END;
$$ LANGUAGE plpgsql;

Next example is more complicated, this is function extracting data from table and processing it before returning via OUT-parameter:

CREATE OR REPLACE PROCEDURE get_balance(
    v_ID IN VARCHAR,
    v_balance OUT NUMBER) 
BEGIN
    SELECT SUM(
        DECODE(
            BALANCE_SIGN, '+', BALANCE_AMOUNT, 
            -1 * BALANCE_AMOUNT)) INTO v_balance 
    FROM ACCOUNTING 
    WHERE 
        ID LIKE v_ID AND 
        months_between(CURRENT_TIMESTAMP, p_date) < 2
    GROUP BY ID;
END;
/
show errors;

Porting this code to PostgreSQL requires implementation of intermediate function for Oracle 'months_between':

CREATE OR REPLACE FUNCTION months_of(interval)
RETURNS int AS $$
    SELECT 
        EXTRACT(years from $1)::int * 12 + 
        EXTRACT(years from $1)::int;
$$ LANGUAGE sql;

And PostgreSQL equivalent of the original function is:

CREATE OR REPLACE FUNCTION get_balance(
    v_ID IN VARCHAR,
    v_balance OUT NUMERIC) 
AS $$
BEGIN
    SELECT SUM(
        (CASE 
        WHEN BALANCE_SIGN='+' THEN BALANCE_AMOUNT
        ELSE -1 * BALANCE_AMOUNT
        END) INTO v_balance 
    FROM ACCOUNTING 
    WHERE 
        ID LIKE v_ID AND 
        abs(months_of(age(CURRENT_TIMESTAMP, p_date))) < 2
    GROUP BY ID;
END;
$$ LANGUAGE plpgsql;

Specialists of Intelligent Converters are always ready to assist in migration of stored procedures, triggers and other database entries. See Database Migration Service for details.