5 Replies Latest reply on Oct 21, 2010 10:28 PM by rareddy

    Creating userdefined data type in Teiid




      Can anyone provide me the steps in creating a userdefined data type in teiid.


      I want to create a userdefined datatype and use it as a input parameter in calling the procedure.

      Is there any way that we can use a Java Class as a input parameter to the Procedure?




        • 1. Re: Creating userdefined data type in Teiid



          Depending on what you are trying to do, the simpliest approach is to just use the OBJECT type for the parameter.



          • 2. Re: Creating userdefined data type in Teiid



            I am creating a procedure to which I want to pass the Java object as the parameter. This java object has some class variables which will be initialized before passing to the procedure and in the procedure I will retrieve the values and use them for some processing.

            • 3. Re: Creating userdefined data type in Teiid

              An OBJECT type would work fine.  Adding a user defined type doesn't currently add much value since the runtime type system only understands built-in types.



              • 4. Re: Creating userdefined data type in Teiid

                I hope that this question is closely enough related to the original post, but here goes.  One of the source databases we use is in PostgreSQL and it utilizes multi-valued fields (a lot - it's a conversion from a VERY legacy system which stores lists all over the place).  Teiid does not appear to have an array data type (and thank heavens for that ...), but I'm wondering if it would be possible to do some type of transformation - possibly involving the Teiid Object data type - in which I can get Field(0).value, Field(1).value, Field(2).value, etc. transformed and mapped to more relational db-friendly datatype (like a concatenated large varchar field).


                I'd appreciate any insight you may have.



                • 5. Re: Creating userdefined data type in Teiid



                  You can read about UDF here http://docs.jboss.org/teiid/7.1.0.Final/developer-guide/en-US/html_single/#udfs


                  Yes, you can accomplish the scenario you described. Use the Designer and import the data from PG, and make sure the column is modeled as Object, and then create the UDF model, and Function that takes Object as in and string as out. Then provide the Java implementation of the function you created and deploy them all. Once you write the function, you can use it in your client SQL code, or you can also use the function inside any Teiid base table transformations or virtual procedures.


                  Hope this helps.