Vertica function with select clause

mpedroso

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.

marcothesane

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 ...

marcothesane

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

Sudhakar_B

@mpedroso,
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.
Thx.

mpedroso

@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.

Example:
select
*,
rescue_hist_charge (employee_code, date) lastcharge
from employees

Function:
var v1 int;

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

return v1;

Sudhakar_B

@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.