How to use DDL statements inside java script UDTF in snowflake

Question

I am trying to use DDL statement like select columns from tables inside java script UDTF.. I am able to achive inside plain UDTF as below

create function returntable()
    returns table(COL1 varchar(100),COL2 VARCHAR(100),COL3 NUMBER(10,0))
    as
    $$
        select COL1,COL2,COL3 from PUBLIC.MYTABLE
    $$;

select * from table(returntable()); - now this returns me a table as output.

but as I need to do some functionalities like conditional if's, case statements, variables, creating temp tables dynamically, and all sorts of typical business requirements which is why I want to write javascript UDTF. I was able to do all those functionalities but unable to write final select * from table inside it, so that it returns my table as output after some transformations.

so, to make it simple, I am trying to achieve something like this

    create or replace function RETURN_TABLE()
    returns table (COL1 varchar(100),COL2 VARCHAR(100),COL3 NUMBER(10,0))
    language javascript
    as
    $$
    {
      processRow: function (row, rowWriter, context){

      /** SOME TRANSORMATIONS USING VARIABLES, TEMP TABLES, IF COMES HERE **/

      rowWriter.writeRow({COL1: ColumnValesfromSelectQuery,COL2: ColumnValesfromSelectQuery,COL3: ColumnValesfromSelectQuery});
      //select COL1,COL2,COL3 from PUBLIC.MYTABLE    
              
        }
        }
    $$;

Can someone please help me on this.

Answer 1

As per Snowflake documentation UDF should not have any DDL statement and when you write it in JavaScript, it does not validate during the compile time but does it during run time.