Hi Experts!
I have a question:
I want to ask if it is possible to create a function/procedure in Vertica that returns sql expression and how do I do it and use it.I have read all the documentation regarding functions and external procedures but I am still not sure I know exactly what to do and how it is possible exactly.
My scenario is that I have specific sql expressions that I run for reporting on a dashboard with variables from the dashboard.This SQL is repeating on almost every query of the dashboard and I would like to store it in the db for the following purposes:
1.for not having to write them on each query that needs them
2.for faster performance.Will it be faster if they are stored in the db?
3.for security reasons I prefer them to be in the db and not on the dashboard itself.
So if my sql looks aprox something like this:
WITH
selected_brands_by_operators AS (
select brand.id as id from brand where brand.operator_id IN ( select id from operator where name IN($arg1) )
),
filtered_orders AS ( select order as filtered_order,currencyId from orders where time between $arg2 and $arg3
and brandId IN (select id from Brand where name IN ($arg4 ) and brandId IN (select id from selected_brands_by_operators)
and deviceType IN (select id from device_types where name IN($arg5 )
and barcodId IN (select id from barcod_info where name IN($arg6 )
)
select * from filtered_orders
This sql is running on my dashboard many times, I want to put it in the db and just call it.A function returns one value and this returns rows.The query doesn't finish there I need to keep manipulating the data according to other dashboard requirements.
Does it make sense?Am I clear enough or more info is needed to understand my question?
Many Thanks for all your help
Keren
