Porting functions with select from postgresql

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?
Tagged:

Comments

  • skeswaniskeswani - Select Field - Employee

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

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

Leave a Comment

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