Create a function which uses a materialized view

Question

From one of my tables in my database I have created a materialized view which is mostly just an aggregated version of that table.
Now I want to create a function that's able to use that materialized view, and perform some query on this.
Creating this function works perfectly fine if I have just created my materialized view, however it seems to fail after some time (for now I noticed it will fail after less than a day, I suspect this has something to do with new data being added in the materialized view?). I can still use this old version of my function on the materialized view, but I somehow won't be able to create a new function that uses this materialized view, even if the new function is the exact same as the old function.
I get an error that says " Sequence contains no elements" and "ActivityStack=(Empty activity stack)".
When I'm trying to run the exact query that I attempt to create a function of, it simply works. So somehow the error happens when using the materialized view inside of the function.
To create the materialized view I used:
.create async materialized-view with (backfill=true) MV on table T { T | query_create_MV }
And for the creation of the function:
.create-or-alter function with (docstring = "text") Function(parameter:type) { MV | query_on_MV }
I can't seem to figure out why this problem occurs, and mostly how to solve this. I haven't been able to find this problem anywhere else...
Thanks in advance

Answer

@HimanshuSinha-msft tThe only problem i see with your the way you are calling the materialized view inside the function is the syntax

it should be like this:

.create-or-alter function with (docstring = "text") Function(parameter:type)
{
materialized_view("MV")
| query_on_MV
}

I guess it will resolve your issue. Just let me know in case you face this issue further

Share via

Create a function which uses a materialized view

1 answer

Your answer