Vertica function with select clause

Hello guys!

I need help with setting up a function. My intention is to pass to this function a set of parameters that will compose a select and the function will return the result of the select. Is it possible to do this in Vertica?

Thank you.


  • Options
    marcothesanemarcothesane - Select Field - Administrator

    Not at this time ... you can, though, code it as SQL generating SQL, and then call it:

    vsql -v -v1="'schema'" -v v2="'table'" -AtXf yourscript.sql | vsql

    A bit rough, but I do that all the time ...

  • Options
    marcothesanemarcothesane - Select Field - Administrator

    note that the string parameter is a double quote, a single quote, then the string, then a single quote, then a double quote....

  • Options
    Sudhakar_BSudhakar_B Vertica Customer ✭✭

    Can you please explain your use case a bit more? It seems like, you want to generate dynamic SELECT statement for performance improvement? I used to do that in traditional DB like Oracle, DB2, and SQL Servers all the time. However in Vertica it is NOT required. Though as suggested by marcothesane, this can be done from shell using scripting or python, this is really not required.
    Please give us more on why you want to do this. May be we can help more.

  • Options

    @all thanks for the replies you guys.

    I am wanting to use a function in a select that queries a given history table and returns the value found in select.

    rescue_hist_charge (employee_code, date) lastcharge
    from employees

    var v1 int;

    select cod_charge into v1 from hist_charge where employee_code = @ {employee_code} and start_date = @ {date}

    return v1;

  • Options
    Sudhakar_BSudhakar_B Vertica Customer ✭✭

    @mpedroso ,
    From your code sample it seems, rescue_hist_charge function is a scalar function & returns a single value. Is that correct?
    If so, you can just join employees and hist_charge table on employee_code and date columns.
    For example:

    select a.*, b.cod_charge
    from employees a join cod_charge b 
    on(a.employee_code = b.employee_code 
    and a.date = b.start_date);

    Hope this helps.

Leave a Comment

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