0

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.

Santhosh
  • 21
  • 1
  • 8
  • Instead of a UDF, you can use a stored procedure for this https://docs.snowflake.com/en/sql-reference/stored-procedures-overview.html – Felipe Hoffa May 06 '21 at 05:23
  • Thanks for the reply @FelipeHoffa, I am trying to get the table as an output but I don't see that option using procedures (I don't even need comma-separated-values, precisely I need a table as output, just like in sql server, we can use select * from table in procedure and it returns a table as output) – Santhosh May 06 '21 at 05:29

1 Answers1

0

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.

H Roy
  • 597
  • 5
  • 10
  • Indeed! The docs say so `Although the body of a UDF can contain a complete SELECT statement, it cannot contain DDL statements or any DML statement other than SELECT.` https://docs.snowflake.com/en/sql-reference/udf-sql.html. Meanwhile DDL statements are allowed in JS procedures. – Felipe Hoffa May 06 '21 at 05:21
  • Is this the case with UDTF too? actually, I could easily use Procedures but i cant return a table from procedures, so I turned this option down. – Santhosh May 06 '21 at 05:26