Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0007412Openbravo ERPA. Platformpublic2009-02-06 12:042017-03-31 14:36
Reporterdalsasua 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityalways
StatusacknowledgedResolutionopen 
PlatformOS20OS VersionrPath Linux
Product Versionpi 
Target VersionFixed in Version 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0007412: substr overloading for postgresql not working
DescriptionIn the Oracle native substr function, when an index of 0 is received as first position of the string, it turns it to a 1. In postgresql, if a 0 is received, then no value is returned as first character, so there exists a function called substr in OpenbravoERP that tries to emulate this behavior:

-- Function: substr(character varying, numeric, numeric)
CREATE OR REPLACE FUNCTION substr(character varying, numeric, numeric)
  RETURNS character varying AS
$BODY$
BEGIN
return substr($1,CAST((CASE $2 WHEN 0 THEN 1 ELSE $2 END) AS INTEGER), CAST($3 AS INTEGER));
END;
$BODY$
  LANGUAGE 'plpgsql' IMMUTABLE
  COST 100;

Actually, this is not working, because when a substr function is invoked, then the postgresql native one is executed. The difference between both functions is in the parameter types:

The one in Openbravo ERP:
substr(character varying, numeric, numeric)

Included in Postgresql catalog:
substr(text, integer, integer)
as well as
substr(bytea, integer)
substr(text, integer)
substr(bytea, integer, integer)

substr function must be re-defined
Steps To ReproduceIn Oracle:

select substr('123',0,2) from dual; -> 12
select substr('123',0,1) from dual; -> 1

In Postgresql:

select substr('123',0,2); -> 1
select substr('123',0,1); -> <empty string>
Proposed Solution
Additional Information
TagsOB3-Reviewed
Relationships
depends on backport 0007793 closed shuehner substr overloading for postgresql not working 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2009-02-06 12:04dalsasuaNew Issue
2009-02-06 12:04dalsasuaAssigned To => rafaroda
2009-02-06 12:04dalsasuasf_bug_id0 => 2572052
2009-02-06 12:04dalsasuaRegression testing => No
2009-02-11 11:50rafarodaAssigned Torafaroda => pjuvara
2009-02-11 14:36pjuvaraNote Added: 0013343
2009-02-11 14:36pjuvaraTypefeature request => defect
2009-02-24 12:10psarobeStatusnew => scheduled
2009-02-24 12:10psarobeAssigned Topjuvara => shuehner
2009-02-24 12:10psarobefix_in_branch => trunk
2011-10-28 10:03psarobeTag Attached: OB3-Reviewed
2012-02-20 11:17shuehnerAssigned Toshuehner => alostale
2012-02-22 15:36alostaleAssigned Toalostale => marvintm
2012-02-22 16:10marvintmAssigned Tomarvintm => AugustoMauch
2012-02-23 10:25AugustoMauchNote Added: 0045394
2012-02-23 10:25AugustoMauchTypedefect => design defect
2012-02-23 10:25AugustoMauchfix_in_branchpi =>
2017-03-31 14:36alostaleStatusscheduled => acknowledged

Notes
(0013343)
pjuvara   
2009-02-11 14:36   
This is not a feature request but a defect since the function does not work as intended.
(0045394)
AugustoMauch   
2012-02-23 10:25   
This issue has been updated to design defect because the change required is too risky.

When a function is called in postgreSQL, the system looks first for its definition in the pg_catalog schema, where all the built-in functions are stored.

In order to be able to override a built-in function(as is the case), we should modify the postgreSQL search path, placing pg_catalog after our schema. This is risky because after that, all overriden functions that are now defined in Openbravo - but not being called - will start being used, and some of that functions might be wrong so the application could stop working.