View source | Discuss this page | Page history | Printable version   

ERP 2.50:Developers Guide/Concepts/DB/PL-SQL code rules to write Oracle and Postgresql code

ERP 2.50:Developers Guide

Index


Contents

Procedure Language rules

Openbravo ERP supports Oracle and PostgreSQL database engines.

This is a set of recommendations for writing Procedure Language that works on both database backends (when possible) or that can be automatically translated by DBSourceManager. These recommendations assume that you have written code in Oracle and that you want to port it to PostgreSQL.


General rules

This a list of general rules that assure that PL runs properly on different database backgrounds.

CASE WHEN variable IS NULL THEN X ELSE Y END

If the variable is the result of concatenating several strings () are required.

SELECT * FROM (SELECT 'X' FROM DUAL) A
SELECT field AS field_name FROM DUAL
UPDATE TABLE_NAME SET (A,B,C) = (SELECT X, Y, Z FROM ..

The following order is correctly accepted:

UPDATE TABLE_NAME SET A = (SELECT X FROM ..), B = (SELECT Y FROM .),C = (SELECT Z FROM ..)
COALESCE(variable_integer, )

do

COALESCE(variable_integer, 0)

to guarantee that it will also work in PostgreSQL.

RIGHT: COALESCE(movementdate, TO_DATE('01-01-1900', 'DD-MM-YYYY'))


Cursors

There are two different ways of using cursors: in FETCH clauses and in FOR loops. For FETCH cursor type no changes are required (except for %ISOPEN and %NOTFOUND methods that are explained below).

Oracle FETCH cursor declarations:

CURSOR	Cur_SR IS

should be translated in PostgreSQL into:

DECLARE Cur_SR CURSOR FOR

For cursors in FOR loops the format suggested is:

TYPE RECORD IS REF CURSOR;
	Cur_Name RECORD;

that is both accepted by Oracle and PostgreSQL.


Arrays

In Oracle, arrays are defined in the following way:

TYPE ArrayPesos IS VARRAY(10) OF INTEGER;
  v_pesos ArrayPesos;
v_dc2 := v_dc2 + v_pesos(v_contador)*v_digito;

but in PostgresSQL they are defined as:

v_pesos integer[];
v_dc2 := v_dc2 + v_pesos[v_contador]*v_digito;


ROWNUM

To limit the number of registers that a SELECT command returns, a cursor needs to be created and read the registers from there. The code could be similar to:

--Initialize counter
v_counter := initial_value;
--Create the cursor
FOR CUR_ROWNUM IN (SELECT CLAUSE)
LOOP
  -- Some sentences
  --Increment the counter
  v_counter := v_counter + 1;
  --Validate condition
  IF (v_counter = condition_value) THEN
    EXIT;
  END IF;
END LOOP;


 %ROWCOUNT

SQL%ROWCOUNT cannot be used directly in PostgreSQL. To convert the SQL%ROWCOUNT into PostgreSQL its value should be defined in a variable. For example:

GET DIAGNOSTICS rowcount := ROW_COUNT;

In place of SQL%ROWCOUNT the previously declared variable should be used.


 %ISOPEN, %NOTFOUND

PostgreSQL cursors do not support %ISOPEN or %NOTFOUND. To address this problem %ISOPEN can be replaced by a boolean variable declared internally in the procedure and is updated manually when the cursor is opened or closed.


Formating code
RAISE NOTICE '%', '@Message@' ;
RAISE EXCEPTION '%', '@Message@' ; --OBTG:-20000--
 IF (CONDITION)
    COMMAND;
 END IF;
 SELECT * into VAR from FUNCTION();
 END ; $BODY$
 LANGUAGE 'plpgsql' VOLATILE
 COST 100;
  :: interval -> to_interval(,)
  :: double precision -> to_number()
Elements not supported by dbsource manager
 columnname::character varying (32)

You should use only character varying or text

DATE_PART('day', end - start) 
date_part('month',now(), '99')
date_part('year',now(),'9999')
m_locator.value::text !~~ 'MRB-%'

Functions

PERFORM and SELECT are the two commands that allow calling a function. Since PostgreSQL does not accept default function parameters we define an overloaded function with default parameters.

To allow the automatic translator to do its job the following recommendations should be followed:


Procedures

There are two ways of invoking procedures from PosgreSQL:


Views

PostgreSQL does not support update for the views. If there is the need of updating a view a set of rules should be created for the views that need to be updated.

In PostgreSQL there are no table/views USER_TABLES or USER_TAB_COLUMNS. They should be created from PostgreSQL specific tables like pg_class or pg_attribute.


Triggers

Rules that the triggers should follow:

IF INSERTING OR (UPDATING AND :OLD.FIELD = ) THEN

The correct way of expressing this is:

IF INSERTING THEN 
... IF UPDATING THEN
IF :OLD.NAME = THEN

If you are using the automatic translator consider that:







ERP 2.50:Developers Guide/Concepts/DB/PL-SQL code infrastructure | ERP 2.50:Developers Guide/Concepts/Middle Tier Fundamentals 

Retrieved from "http://wiki.openbravo.com/wiki/ERP_2.50:Developers_Guide/Concepts/DB/PL-SQL_code_rules_to_write_Oracle_and_Postgresql_code"

This page has been accessed 15,387 times. This page was last modified on 12 August 2013, at 08:36. Content is available under Creative Commons Attribution-ShareAlike 2.5 Spain License.