You can translate the question and the replies:

Postgres ADDDAY translates, but other ADD datetime functions do not

We recently started using a Postgres connection in Denodo. We've noticed that on larger datasets (particularly in joins) that performance is impacted when using the ADDMONTH function. ADDMONTH can't be delegated to the data source since it's not a Postgres function. Larger datasets must handle the ADDMONTH at the Denodo engine level which impacts performance. This is expected. However, we found that the ADDDAY function does delegate to the data source, even though ADDDAY is not a Postgres function. The adapter translates ADDDAY to " + INTERVAL 'x day' ", like below: SELECT t0.create_date AS create_date, (t0.create_date + (? * interval '1 day')) AS dd FROM postgresdbname.table t0 Why does the adapter translate ADDDAY properly, but not ADDMONTH? Is this a custom scalar function built into the Denodo engine? If so, how can we get Denodo to add the other Postgres ADD functions to translate properly as well?
PostgreSQL datetime Date time function
user
05-05-2021 11:43:34 -0400
code

3 Answers

Hi, To get the list of the scalar functions currently delegated to PostgreSQL, you can navigate to the “Delegate scalar function list” in the Source Configuration tab of the data source. ADDDAY is on this list whereas ADDMONTH is currently not on the list. Denodo does not delegate ADDMONTH function because PostgreSQL does not have an equivalent function to map. So it can only be executed in Virtual DataPort but not delegated. I would develop a custom function to delegate the ADDMONTH functionality in PostgreSQL. For more information on how to develop this, refer to the document [Developing Custom Functions that Can Be Delegated to a Database](https://community.denodo.com/docs/html/browse/latest/en/vdp/developer/developing_extensions/developing_custom_functions/creating_custom_functions_with_annotations#developing-custom-functions-that-can-be-delegated-to-a-database). If you are a valid support user and would like the delegation to be considered in a future release, you can open a support case in the Denodo Support Site with an enhancement request so that it can be evaluated by the team. Hope this helps!
Denodo Team
07-05-2021 17:25:37 -0400
code
I understand the list of delegated scalar functions, but ADDDAY() is NOT a valid or supported function within Postgres. So my question is.. how and where is Denodo translating ADDDAY() to use the INTERVAL syntax? If I look at the execution plan, Denodo is changing ADDDAY to use INTERVAL. SELECT t0.create_date AS create_date, (t0.create_date + (? * interval ‘1 day’)) AS dd FROM postgresdbname.table t0 Why does the adapter translate ADDDAY properly to use the INTERVAL syntax when it's in the delegation list, but not ADDMONTH and the other ADD datetime functions when they are in the delegation list? Something is translating this ADDDAY function.
user
10-05-2021 17:54:05 -0400
Hi, I would develop a custom function to delegate the ADDMONTH functionality in PostgreSQL. However, if you are a valid support user and if you want ADDMONTH to be delegated in the same manner as ADDDAY to PostgreSQL, then you can open a support case as an Enhancement request on [Denodo Support Site](https://support.denodo.com/) so that the support team can help you. Hope this helps!
Denodo Team
20-05-2021 21:10:43 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here