Postgresql is a global variable similar to oraclesql % rowcount

Source: Internet
Author: User
Tags oracle substr
Wiki. openbravo. comwikiERP2.50Developers _ GuideConceptsDBPL-SQL_code_rules_to_write_Oracle_and_Postgresql_codeProcedureLanguagerulesOpenbravoERPsupportsOracleandPostgreSQLdatabaseengines.Thisisasetofrecommendat

Http:// Procedure Language rules Openbravo ERP supports Oracle and PostgreSQL database engines. This is a set of recommendat


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 byDBSourceManager. 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.

  • JOIN statement. Change the code replacing the (+) by left join or RIGHT JOIN
  • In XSQL we use a questionmark (?) As parameter placeholder. If it is a NUMERIC variable use TO_NUMBER (?). For dates use TO_DATE (?).
  • Do not use GOTO since PostgreSQL does not support it. To get the same functionality that GOTO use boolean control variables and IF/THEN statements to check if the conditions are TRUE/FALSE.
  • Replace NVL commands by COALESCE.
  • Replace DECODE commands by CASE. If the CASE is NULL the format is:

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

  • Replace SYSDATE commands by NOW ()
  • PostgreSQL treats "" and NULL differently. When checking for a null variable special care is required.
  • When a SELECT is inside a FROM clause a synonym is needed.
  • When doing SELECT always use.
SELECT field AS field_name FROM DUAL
  • PostgreSQL does not support synonyms of tables in UPDATE, INSERT or DELETE operations.
  • PostgreSQL 8.1 (but fixed in 8.2 version) does not support updates like:

The following order is correctly accepted:

  • PostgreSQL does not support using the table name in the fields to update.
  • PostgreSQL does not support the delete table command. delete from Table shocould be used instead.
  • PostgreSQL does not support parameters like '1' in the order by or group by clause. A numeric without commas can be used.
  • PostgreSQL does not support the current of clause. An active checking of register to update shocould be used.
  • PostgreSQL does not support COMMIT. It does automatically an explicit COMMIT between begin end blocks. Throwing an exception produces a ROLLBACK.
  • PostgreSQL does not support SAVEPOINT. BEGIN, END and ROLLBACK shocould be used instead to achieve the same functionality.
  • PostgreSQL does not support CONNECT.
  • Both Oracle and PostgreSQL do not support using variable names that match column table names. For example, use v_IsProcessing instead of IsProcessing.
  • PostgreSQL does not support execute immediate... USING. The same functionality can be achieved using SELECT and replacing the variables with parameters manually.
  • PostgreSQL requires () in callto functions without parameters.
  • DBMS. OUTPUT shoshould be done in a single line to enable the automatic translator building the comment.
  • In PostgreSQL any string concatenated to a NULL string generates a NULL string as result. It's recommended to use a COALESCE or initialize the variable ''.
    • NoticeThat in Oracle null | 'A' will return 'a' but in PostgrSQL null, so the solution wocould be coalesce (null ,'') | 'A' that will return the same for both. but if the we are working with Oracle's NVarchar type this will cause an ORA-12704: character set mismatch error, to fix it is possible to use coalesce (to_char (myNVarCharVariable ),) | 'A '.
  • Instead of doing
COALESCE(variable_integer, )


COALESCE(variable_integer, 0)

To guarantee that it will also work in PostgreSQL.

  • PostgreSQL does the select for update at a table level while Oracle does it at a column level.
  • PostgreSQL does not support INSTR command with three parameters. SUBSTR shocould be used instead.
  • In Oracle SUBSTR (text, 0, Y) and SUBSTR (text, 1, Y) return the same result but not in PostgreSQL. SUBSTR (text, 1, Y) shocould be used to guarantee that it works in both databases.
  • PostgreSQL does not support labels like <> (But it can be commented out ).
  • In dates comparisons is often needed a default date when the reference is null,January 1, 1900.OrDecember 31,999 9Shocould be used.
  • When is specifiedDate as a literalIt is necessary to useAlwaysThe to_date functionWithThe correspondentFormat mask.
RIGHT: COALESCE(movementdate, TO_DATE('01-01-1900', 'DD-MM-YYYY'))


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

Oracle FETCH cursor declarations:


Shocould be translated in PostgreSQL:


For cursors in FOR loops the format suggested is:


That is both accepted by Oracle and PostgreSQL.


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 processing ssql they are defined:

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


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 cocould be similar:

--Initialize counterv_counter := initial_value;--Create the cursorFOR 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;


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


In place of SQL % ROWCOUNT the previusly declared variable shocould be used.


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
  • To export properly a raise notice from postgresql to xml files you have to follow this syntax:
RAISE NOTICE '%', '@Message@' ;
  • To export properly a raise exception from postgresql to xml files you have to add the following comment at the end of the command; -- OBTG:-20000 --
RAISE EXCEPTION '%', '@Message@' ; --OBTG:-20000--
  • In a IF clause is very important to indent the lines within the IF.
  • The functions with output parameters have to be invoked with select *.
 SELECT * into VAR from FUNCTION();
  • The end of the functions have to be defined as following to be exported properly:
  • The cast used by postgresql is not supported by Dbsourcemanager. Instead of using: type, use a function to convert the value
  :: interval -> to_interval(,)  :: double precision -> to_number()
Elements not supported by dbsource manager
  • Functions that return "set of tablename"
  • Functions that return and array
  • Functions using regular expresions
  • Column with type not supported ded on the table on the following document:

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 shoshould be followed:

  • The AS and the IS shoshould not contain spaces to the left
  • The function name shocould not be quoted
  • In functions, the END shoshould go at the beginning of the line without any spaces to the left and with the function name.


There are two ways of invoking procedures from PosgreSQL:

  • Using the format variable_name: = Procedure_Name (...);
  • Using a SELECT. This is the method used for procedures that return more than one parameter.


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

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


Rules that the triggers shoshould follow:

  • As general rule, is not desirable to modify child columns in a trigger of the parent table, because it is very usual that child trigger have to consult data from parent table, originMutating table error.
  • The name shoshould not be quoted (") because PostgreSQL interprets it literally.
  • All the triggers have a DECLARE before the legal notice. In PostgreSQL it is necessary to do a function declaration first and then the trigger's declaration that executes the function.
  • PostgreSQL does not support the OF... (columns)... ON Table definition. It is necessary to include the checking inside the trigger.
  • PostgreSQL does not support lazy evaluation. For example the following sentence works in Oracle but not in PostgreSQL:

The correct way of expressing this is:

  • Triggers in PostgreSQL always return something. depending on the type of operation it returns OLD (DELETE) or NEW (INSERT/UPDATE ). it shoshould be placed at the end of the trigger and before the exceptions if there are any.

If you are using the automatic translator consider that:

  • The last EXCEPTION in the trigger shocould not have spaces to its left. The translator considers this the last exception and uses it to setup the right return value.
  • The last END shoshould not have spaces to its left. The indentation is used to determine where function ends.
  • Beware that if you add an statement like "IF TG_OP = 'delete' then return old; else return new; end if;" just before the EXCEPTION statement, it might be removed by the automatic translator.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.