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

pkosew

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

SudhakarB

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

pkosew

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.