0007793: substr overloading for postgresql not working

Openbravo Issue Tracking System - Openbravo ERP

View Issue Details

Project

Category

View Status

Date Submitted

Last Update

0007793

Openbravo ERP

A. Platform

public

2009-02-06 12:04

2012-02-20 11:41

Reporter

dalsasua

Assigned To

shuehner

Priority

normal

Severity

minor

Reproducibility

always

Status

closed

Resolution

out of date

Platform

OS Version

rPath Linux

Product Version

Target Version

Fixed in Version

Merge Request Status

Review Assigned To

OBNetwork customer

Web browser

Modules

Core

Support ticket

Regression level

Regression date

Regression introduced in release

Regression introduced by commit

Triggers an Emergency Pack

Summary

0007793: substr overloading for postgresql not working

Description

In 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 Reproduce

In 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