Re: UDF's in Views

joe.goray@xxxxxxxxx wrote:

If you include calls to UDFs within an SQL VIEW and have
an SQL statement against that view that does not request
results from the UDFs, are they still executed anyway?

Example:

VIEW with UDF:

create view viewWithUdf (fld_1, fld_2, result_1, result_2)
as select fld_1, fld_2, udf_1(fld_1,fld_2) as result_1
, udf_2(fld_2) as result_2
from myFile

SQL statement using VIEW:

select fld_1, result_1 from viewWithUdf

In the above example, will udf_2 be called even though it
is not being requested? And will udf_1 be able to be called
successfully even though fld_2 wasn't requested?

With the expressions in the underlying column definitions being merged, the given SQL SELECT statement would not even refer to the UDF_2. Similarly the UDF_1 has specified as its inputs, both of FLD_1 & FLD_2, each from the TABLE that is referenced by the VIEW in the FROM clause. The statement can be rewritten as:
"SELECT fld_1, udf_1(fld_1,fld_2) as result_1 FROM myFile"

As such, the answers are:

- The UDFs *may* be performed. That depends on how the optimizer implements the query. If the chosen implementation is to perform the entire VIEW SELECT as a temporary result, then the UDFs will be invoked at least for every row of that temporary result set. Only a query allowing static results could use the temporary result as its implementation. Other implementations could perform the UDFs, but according to the scenario, would not need to perform them; simply, it depends on what the optimizer and query engine decide to do.

- Given the SELECT query of the VIEW is rewritten as alluded above [the merged expressions], the reference to UDF_2 would be omitted [as would be expected]. In that case, the UDF_2 would not be invoked for the given SELECT.

- The FLD_2 refers to the column by that name in the TABLE. The column RESULT_1 defines & encapsulates an expression in the VIEW as UDF_1(FLD_1,FLD_2). That expression has implicitly included a path [mapping] to the FLD_2 of the TABLE, but solely for evaluation of the expression. Thus the SELECT against the VIEW [as defined in the given CREATE] cannot refer to the name FLD_2 directly because no column FLD_2 was named in the (column-list) of the CREATE VIEW, regardless that the VIEW has implicit access to the data in FLD_2 [the mapping] in order to evaluate the expression as column RESULT_1.

Regards, Chuck