you mean a source (like Oracle, SQLServer) specific function or write your own to be used in user SQL statements? You can do both, but UDF typically means later one. See documentation here

Support for User-Defined Functions(Non-Pushdown) · Teiid Documentation

essentially you need to add details about functions in view model DDL, then supply the respective jar files in your classpath.

Ramesh..

Ramesh,

Thank you for your quick response. We are trying to add a non-push down custom function (i.e., a non-source specific function).

We are trying to create it programmatically (i.e without using XML descriptor). A previous post on this topic indicates that it can be done using MetadataFactory.addFunction.

We haven't had much success using it.

Here's an excerpt from the code we are trying to use -

EmbeddedServer es = new EmbeddedServer();

..

es.start(config);

try {

MySQL5ExecutionFactory mysql = new MySQL5ExecutionFactory();

mysql.start();

es.addTranslator("mysql5",mysql);

DataSource ds = ...

..

ModelMetaData md = new ModelMetaData();

md.setName("test");

md.addSourceMapping("test","mysql5","java://localDS");

//query with custom non-pushdown func

md.setSchemaText("create view \"actor\" (name varchar(300)) as select my_custom_func(first_name) as first_name from \"test.actor\");

//define custom function - this part needs clarity

MetadataFactory mf = new MetadataFactory("test",1,null,md);

mf.addFunction("my_custom_func", StringOperation.getMethod("toUpperCase", String.class));

mysql.getMetadata(mf,null);

//deploy code

..

}

I would appreciate any leads here on how to make this code work. Thanks!    

See an example here teiid/TestMetadataFactory.java at master · teiid/teiid · GitHub

There is no need for Function Model, that is so Teiid 7 or something. There is no restriction on where the function is defined in later Teiid versions. If you define a function on a "view" model it is a UDF, the same function if you define on a "source" model then it is a "pushdown" function. A UDF needs to be backed by the Java methods, the documentation shows how to write one here [1].

Post the code what you have written and SQL you are trying to use with maybe we can see why engine thinks it is in the wrong form. Also, sometimes you need to call as "mymodel.toLowerCase9(...)" to qualify it.

[1] Support for User-Defined Functions(Non-Pushdown) · Teiid Documentation

You use of MetadataFactory is not consistent. I am not sure how you are building the end VDB. You can take a simpler route like by defining the Function in the DDL itself. For example:

public class TestFunction {
    EmbeddedServer es;
    
    @Before 
    public void setup() {
        es = new EmbeddedServer();
    }
    
    @After 
    public void teardown() {
        if (es != null) {
            es.stop();
        }
    }
    
    @Test public void testDDLWay() throws Exception {
        EmbeddedConfiguration ec = new EmbeddedConfiguration();
        ec.setUseDisk(false);
        es.start(ec);
        
        es.addTranslator("loopy", new LoopbackExecutionFactory());
        
        ModelMetaData sourceModel = new ModelMetaData();
        sourceModel.setName("source");
        sourceModel.addSourceMapping("x", "loopy", null);
        sourceModel.addSourceMetadata("DDL", "CREATE FOREIGN TABLE actor (e1 integer PRIMARY KEY, first_name varchar(25), last_name double);");

     
        String schema = "CREATE VIRTUAL FUNCTION toLowerCase(IN \"value\" varchar) RETURNS varchar(300) " +
                "OPTIONS(CATEGORY 'misc', JAVA_CLASS 'org.teiid.runtime.StringOperation', JAVA_METHOD 'toLowerCase');"
                + "create view actor_view(first_name varchar(300)) as select toLowerCase(first_name) from actor;";
        
        ModelMetaData viewModel = new ModelMetaData();
        viewModel.setName("virt");
        viewModel.setModelType(Type.VIRTUAL);
        viewModel.addSourceMetadata("DDL", schema);


        es.deployVDB("test", sourceModel, viewModel);
        
        TeiidDriver td = es.getDriver();
        Connection c = td.connect("jdbc:teiid:test", null);
        Statement s = c.createStatement();
        ResultSet rs = s.executeQuery("select * from \"actor_view\"");
        while(rs.next()) {
            System.out.println(rs.getString(1));
        }
    }    
}

If you want use MetadataFactory, you need show an example of how you are building the VDB.

Ramesh..

You can Thank me by writing me a blog about Teiid, why you used and how it helped your usecase