Date compatible Function from Oracle to Postgres

Question

I am trying to convert the below definition to Postgres.

Current Oracle code:

PROCEDURE Run_All (inDate DATE DEFAULT SYSDATE) IS

In Postgres I used different versions:

CREATE OR REPLACE FUNCTION ssp2_pcat.gen_fios_xml$run_all
   (indate timestamp(0) DEFAULT CURRENT_DATE)

CREATE OR REPLACE FUNCTION ssp2_pcat.gen_fios_xml$run_all
   (indate timestamp without timezone DEFAULT ('now'::text)::date)

CREATE OR REPLACE FUNCTION ssp2_pcat.gen_fios_xml$run_all
   (indate date DEFAULT ('now'::text)::date)

CREATE OR REPLACE FUNCTION ssp2_pcat.gen_fios_xml$run_all
   (indate timestamp(0) DEFAULT CURRENT_TIMESTAMP::timestamp(0))

But still it's throwing an error as below:

ERROR:  column "timestamp" does not exist
LINE 1: SELECT TIMESTAMP
               ^
QUERY:  SELECT TIMESTAMP
CONTEXT:  PL/pgSQL function "gen_fios_xml$run_all"(date) line 13 during statement block local variable initialization
SQL state: 42703

What would be the right way to convert, am I missing something in Postgres? Any update is absolutely appreciated!

Laurenz Albe · Accepted Answer · 2018-11-14 19:19:46Z

1

The error is not in the part of the function that you are showing, it is in line 13 of the function body in a SELECT statement.

Your function signatures should work, but if you need the hour-to-second part of the Oracle DATE, the perfect translation would be:

CREATE FUNCTION ssp2_pcat.gen_fios_xml$run_all
    (indate timestamp(0) without time zone DEFAULT localtimestamp(0))

answered Nov 14, 2018 at 19:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

user10531062 Over a year ago

Ok, Really appreciate your time but Still the same error,! What can be the right signature that I can use here for running the Function Sirr.?

user10531062 Over a year ago

Thanks for your time in looking into this Laurenz,you are right, its a datatype mismatch in a variable in the source code that caused the error!

Collectives™ on Stack Overflow

Date compatible Function from Oracle to Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related