The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.