0046240: dbsourcemanager export database model does not support postgresql typecasting

Description

In postgresql you can do things like this:
WHERE dt.date_actual > (now() - '2 years'::interval)

When using this in a materialized view and exporting it through the reporting server export action (which uses dbsourcemanager) it removes the '::interval' causing incorrect sql to be stored in our source code repos.

We have to manually repair the sql before pushing.

I saw the same with other type casting. This can also give trouble when calling postgresql functions as without type inference the function is not recognized.

Steps To Reproduce

A simple example mv which would go wrong. Not sure if the dbsourcemanager out of the box exports materialized views.

-- DROP MATERIALIZED VIEW public.m_test_export;

CREATE MATERIALIZED VIEW public.m_test_export
TABLESPACE pg_default
AS
SELECT o.ad_org_id
   FROM c_order o
  WHERE o.created > (now() - '2 years'::interval)
WITH NO DATA;

ALTER TABLE public.m_test_export
    OWNER TO tad;

Issue History
Date Modified	Username	Field	Change
2021-04-09 14:44	mtaal	New Issue
2021-04-09 14:44	mtaal	Assigned To	=> platform
2021-04-09 14:44	mtaal	Modules	=> Core
2021-04-09 14:44	mtaal	Triggers an Emergency Pack	=> No
2022-02-01 07:24	alostale	Assigned To	platform => Triage Platform Base

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
There are no notes attached to this issue.