function oracle to postgresql

Question

I'm migrating an Oracle database to PostgreSQL, to transfer the tables I had no problem, however I'm having trouble transcribing a function for it to run in Postgres, below the function found in Oracle:

create or replace FUNCTION  "FN_HOUR_MINUTE" (P_HOUR IN NUMBER) 
RETURN NUMBER 
IS
  -- PL/SQL Specification
  V_RETORN                 NUMBER(4);
  -- Convert hour to minute
  -- PL/SQL Block
BEGIN
V_RETORN := 60*TO_NUMBER(SUBSTR(LTRIM(TO_CHAR(P_HOUR,'0000'),' ') ,1,2))+
                  TO_NUMBER(SUBSTR(LTRIM(TO_CHAR(P_HOUR, '0000'),' '), 3,2));

RETURN V_RETORN;
EXCEPTION
WHEN OTHERS THEN
       RETURN NULL ;
END;

I tried writing in postgres as follows:

CREATE OR REPLACE FUNCTION fn_hour_minute(p_hour in NUMERIC)
  RETURNS NUMERIC(4) AS $$
DECLARE
  v_retorn NUMERIC(4);
BEGIN
  v_retorn := 60*TO_NUMBER(SUBSTR(LTRIM(TO_CHAR(p_hour,'0000'),' ') ,1,2))+
                   TO_NUMBER(SUBSTR(LTRIM(TO_CHAR(p_hour, '0000'),' '), 3,2));
  RETURN v_retorn;
END;
$$ LANGUAGE plpgsql;

But gives an error that says the to_number function does not exist.

try TO_NUMBER(SUBSTR(LTRIM(TO_CHAR(p_hour,'0000'),' ') ,1,2), '0000') Also, why are you converting to character then to number? — CBredlow
– CBredlow, Commented Feb 28, 2018 at 20:36
It worked, thank you. I did not quite understand why he did these two conversions, the Oracle database was not made by me, but that function worked there in that way. — Allan Canuto
– Allan Canuto, Commented Feb 28, 2018 at 20:49

krokodilko · Accepted Answer · 2018-02-28 20:52:23Z

2

If you spread the expression into factors:

select TO_CHAR(1234,'0000'),
    ltrim(TO_CHAR(1234,'0000')),
    substr(ltrim(TO_CHAR(1234,'0000')),1,2),
    substr(ltrim(TO_CHAR(1234,'0000')),3,2)
from dual;

TO_CH LTRIM SU SU
----- ----- -- --
 1234 1234  12 34

you will see that this is just a very advanced way to calculate such an expression

60 * TRUNC( p_hour / 100 ) + p_hour % 100

answered Feb 28, 2018 at 20:52

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

CBredlow · Accepted Answer · 2018-02-28 20:50:26Z

0

You forgot to include the formating for the TO_NUMBER section. Update the TO_NUMBER to TO_NUMBER(SUBSTR(LTRIM(TO_CHAR(p_hour,'0000'),' ') ,1,2), '0000') and it should work.

answered Feb 28, 2018 at 20:50

CBredlow

2,8502 gold badges31 silver badges51 bronze badges

Collectives™ on Stack Overflow

function oracle to postgresql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related