I'm the author of the extension that the vtab and define function in that module were adapted from. It allows you to create something like a parameterized view, but the way it works is fairly simple: a prepared statement is created from the provided SQL on the same db connection as the vtab, and is executed each time the virtual table is queried. Parameters can be supplied as constraints (or using table valued function syntax) and the results of executing the statement are the row values.
Did you have any questions in particular?