stored procedure style functionality - clarification needed
Hi,
I'm working on a project using Tableau reports against Vertica, and have a requirement to produce a result set for a dashboard based on parameters defined at run time.
As i see it, this is classic stored procedure territory, ie passing values to a procedure held in the db which dynamically generates a query and returns the result set to the caller.
However, having looked at this for a while, there doesn't appear to be a way/workaround to achieve this.
So far I've considered/discounted the following ...
a) Tableau custom sql data sources (I know this is out-of-scope for this forum but i'm referring to it for completeness), however for various Tableau based issues this isn't a solution.
b) User defined transform functions.
As I understand it, a UDTF is effectively a row level function, using the column values for each row in the result set collected by the calling query, eg
select udtf(a.col1, a.col2) from testtable a where a.col2 = 'conditionvalue'
where the effect of the where clause condition occurs before the udtf is called, rather than taking parameters and generating a new result set itself, ie
select * from udtf('conditionvalue')
yes a udtf can return > 1 row for each row passed to it, but the main result set is determined by the sql query calling the udtf and its where clause conditions.
c) External procedures.
I've created a test external procedure which creates a view dynamically based on parameters passed to it in the calling query, eg
select extproc('conditionvalue');
select * from extproc_view;
(where extproc_view is dropped and then created by extproc as select col1, col2 from testtable where col2= '$1')
however, Tableau isn't able to run 'select extproc('conditionvalue')' due to ;
ERROR 3934: Meta-function ("extproc") can be used only in the Select clause
which is similar to the errors Vertica returns when I call extproc in a query style anything other than
'select externalprocedurename(arguments)'
Obviously for this approach, Tableau would need to call the extproc to generate a result set, and then query the view.
The only remaining option I can see is to look at our design again to try to avoid dynamically generating result sets, however, before doing that, I'd appreciate a steer as to whether I'm missing an option d) or if I'm wrong with any of the before.
Thanks,
Steve
Comments
Apologies for the looooong post and appreciate its only been 1 day but this is quite urgent, so I'd appreciate a quick answer from HP to confirm if I've missed anything.
Thanks again,
Steve