Search code examples
oracle-databasepostgresqlpolyfillsshim

Any Polyfills / Shim for Oracle functions to be used in postgresql


There is a java jar file/external library with no source... It connects to database engines through jdbc and few of the sql queries that it makes are ...TRUNC(date_column,'Q')..., ...TRUNC(date_column,'DD')... and ...TRUNC(date_column,'MM')... but postgresql db Trunc() method handles only numeric data and the exact function to truncate date is date_trunc()...

the order of the parameters also changes for Trunc() and date_trunc()

So Is there a postgresql custom function that can overload the existing trunc() function and handle all the formatters 'DD', 'MM', 'Q' and so on(other formatters reference)...?

I could try to write a new function from scratch... but it seems like reinventing the wheel... any reusable polyfill sql pack/file that I can use?

So far, I've come up with this:

CREATE OR REPLACE FUNCTION trunc(dtval timestamp with time zone, formatter text) returns timestamp with time zone AS $body$
BEGIN
RETURN date_trunc((CASE
    WHEN formatter IN ('MI') THEN 'minute'
    WHEN formatter IN ('HH','HH12','HH24') THEN 'hour'
    WHEN formatter IN ('DAY','DY','D') THEN 'day'
    WHEN formatter IN ('DDD','DD','J') THEN 'day'
    WHEN formatter IN ('W') THEN 'week'
    WHEN formatter IN ('IW') THEN 'week'
    WHEN formatter IN ('WW') THEN 'week'
    WHEN formatter IN ('MONTH','MON','MM','RM') THEN 'month'
    WHEN formatter IN ('Q') THEN 'quarter'
    WHEN formatter IN ('IYYY','IY','IY','I') THEN 'year'
    WHEN formatter IN ('SYYYY','YYYY','YEAR','SYEAR','YYY','YY','Y') THEN 'year'
    WHEN formatter IN ('CC','SCC') THEN 'year'
ELSE 'day'
END),dtval);
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
STABLE;

Am I on the right path?


Solution

  • Have you looked at github.com/orafce/orafce ?