Porting functions with select from postgresql

rribas

I'm porting some tables and queries from postgresql, however they make use of functions that contain select statements. I found out that vertica doesn't allow that. What options do I have in this case? As far as I can tell I could do one of the following:

• Create an external procedure, that would run the select. I feel like that would have a bad performance.
• Get rid of the function and inline it in the query. The disadvantage is that I have to significantly modify the existing queries and also repeat the same logic on all queries using it.
• Anything else I can do?

skeswani

can you provide an example. I might be able to better understand the problem with an example

rribas

Let's say I have a function the determines cost of a product, let's say it is a function a couple of things including fees based on product category and fee based on specific products.

so there is function looks like this:

create function calculate_fee(product varchar, category varchar, vendor varchar)
    return float
as
begin
    return
        (select sum(fee) from vendor_fees where vendor = vendor) +
        (select sum(fee) from category_fee where category = category) +
        (select sum(fee) from product_fee where product = product);
end

and then that function is used in queries that need to, say, calculate amount spent on various products, like this:

select sum(price + calculate_fee(product, category, vendor)), department
group by department;

In this case, I could just replace the function call with the function code. But I would prefer not to have to do that. For example, if there are other functions and queries doing similar things, it would take a lot of work to convert all the queries to stop using the functions.