Function reference automatically replaced with formula in view code - how to prevent this?

pkosewpkosew Registered User

When I create a view that calls a function, function reference is replaced by its formula (simplified if possible).
Is there a way to prevent it?

Example:

CREATE FUNCTION f (x NUMERIC) RETURN NUMERIC AS
BEGIN
RETURN 2*x;
END;

CREATE VIEW v_f AS
SELECT f(1) AS v;

The actual code of this view is:
CREATE VIEW v_ft
AS
SELECT 2 AS v FROM v_catalog.dual

Answers

  • SudhakarBSudhakarB Registered User

    @OP,
    Per Vertica documentation, this is the expected behavior.
    Documentation
    MY question is why would you want to prevent this behavior?
    Just want to understand the use-case.

    • SQL Macros are flattened in all cases, including DDL.
    • You can create views on the queries that use SQL functions and then query the views. When you create a view, a SQL function replaces a call to the user-defined function with the function body in a view definition. Therefore, when the body of the user-defined function is replaced, the view should also be replaced.
    • If you want to change the body of a SQL function, use the CREATE OR REPLACE syntax. The command replaces the function with the new definition. If you change only the argument name or argument type, the system maintains both versions under the same function name. See Examples section below.
  • pkosewpkosew Registered User

    OK, is there a way to override this? I'd like Vertica to keep and run the view exactly as I've coded.
    I understand the importance of flattening for performance, but a choice would be welcome - much like you can modify the behaviour of optimizer in other databases.

    As for a use-case - I don't think it really matters.
    One of fundaments of functions in programming is that they enclose a piece of code, which can be easily modified (fixed, developed further).

    With forced flattening, functions in Vertica are just code shorthands. I find this design choice very weird and limiting...

    Basically, I'll have to script all objects in the database and overwrite everything that references a modified function.

Leave a Comment

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