Hi,
Customer has migrated form SQL Server to Vertica. The functionality he is missing a lot is a "Stored Procedures" , allowing end users (non-admin) to execute parametrized SQL query template (that must be managed centraly) and be able to get the result set (i.e. for reporting)....
Customer wants to have this functionality, as query might be very complex , and such queries are too difficult for business users to edit or play with. Also quite often such queries are used by some reports (Qlik), so changing everywhere and keep the proper and same version is difficult.
My idea was to:
1. Create function that accepts desired parameters values (passed as function arguments) , and returns a string being query to be executed (with proper value substitution in predicate section).
2. Grab that string and run as an VERTICA external procedure, that could store returned dataset as temporary table. Temporary table name, that must be unique.... (many users can run the same flow in parallel)
The issue i cans see is how to manage temporary table names....
Is there any other way to fullfil customer's requirement????
Any hints?
Maciej
