0035497: Support recursive CTEs

Description

Recursive CTEs work fine in Oracle and PostgreSQL supported by Openbravo versions, however the DBSM is unable to properly work with them.

In PostgreSQL the syntaxis is: WITH RECURSIVE …
In Oracle the syntaxis is: WITH …

The DBSM should export to XML the "WITH RECURSIVE" always, but depending on the database, the way to apply it should be different:
PostgreSQL should have the same syntaxis as in the XML
Oracle should remove the keyword "RECURSIVE" and add it as a note somewhere so it can be properly understood by the DBSM when exporting the database

Steps To Reproduce

Create a function like this one in PostgreSQL:

CREATE OR REPLACE FUNCTION ad_isorgincluded_cte(
    p_orgid character varying,
    p_parentorgid character varying,
    p_clientid character varying)
  RETURNS numeric AS
$BODY$ DECLARE
/*************************************************************************
* The contents of this file are subject to the Openbravo Public License
* Version 1.1 (the "License"), being the Mozilla Public License
* Version 1.1 with a permitted attribution clause; you may not use this
* file except in compliance with the License. You may obtain a copy of
* the License at http://www.openbravo.com/legal/license.html [^]
* Software distributed under the License is distributed on an "AS IS"
* basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
* License for the specific language governing rights and limitations
* under the License.
* The Original Code is Openbravo ERP.
* The Initial Developer of the Original Code is Openbravo SLU
* All portions are Copyright (C) 2001-2006 Openbravo SLU
* All Rights Reserved.
* Contributor(s): ______________________________________.
************************************************************************/
/*************************************************************************
* Title: Return 'Org Level' if orgID is a leaf of the org tree
* where parentOrgID is the root. Else return -1
************************************************************************/
  v_Level NUMERIC:=0;
  v_treeID VARCHAR(32) ; --OBTG:varchar2--
  v_Parent VARCHAR(32) ;
  v_Node VARCHAR(32) ;
BEGIN
  SELECT MAX(AD_TREE_ORG_ID)
  INTO v_treeID
  FROM AD_CLIENTINFO
  WHERE AD_CLIENT_ID=p_clientID;
  v_Parent:=p_OrgID;

  WITH RECURSIVE organizationtree(levelno, orgId, parentOrgId)
AS (
  select 2, node_id, PARENT_ID
  from AD_TREENODE
  where AD_TREE_ID=v_treeID
  and node_id = p_orgid
  union all
  select levelno+1, tn.NODE_ID, tn.PARENT_ID
  from organizationtree ot
  join ad_treenode tn on (ot.parentOrgId = tn.NODE_ID
  and tn.AD_TREE_ID=v_treeID
  and tn.PARENT_ID NOT IN ('0', v_Parent))
)
select ot.levelno
into v_Level
from organizationtree ot
where ot.parentOrgId = p_parentorgid;

return v_Level;
END ; $BODY$
  LANGUAGE plpgsql STABLE
  COST 100;

Execute it and verify it works fine. Example:
select *, ad_isorgincluded(ad_org_id, '0', '23C59575B9CF467C9620760EB255B389') from ad_org

Export the database, verify it is properly exported

Apply the diff to an Oracle instance and update the database.
Verify it seems to be properly updated.
Try to execute the function and verify an error is raised. Oracle doesn't understand the RECURSIVE keyword. If you manually edit the function accordingly it will work fine in Oracle

Issue History
Date Modified	Username	Field	Change
2017-03-13 14:24	vmromanos	New Issue
2017-03-13 14:24	vmromanos	Assigned To	=> platform
2017-03-13 14:24	vmromanos	Modules	=> Core
2017-03-13 14:24	vmromanos	Triggers an Emergency Pack	=> No
2022-02-01 08:09	alostale	Assigned To	platform => Triage Platform Base

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.