Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

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