1

I want the table names in a string variable Object_List as below Object_List=('Table1,Table2,Table3) and want the output as a Table of Values RowNo TableName 1 Table1 2 Table2 3 Table3.

The code giving error is as below:

CREATE OR REPLACE FUNCTION Extract_TableNames(Object_List Varchar)

RETURNS TABLE ("TABLE_NAME" VARCHAR)

LANGUAGE SQL

AS

'

WITH DATATABLE(VALUEA) AS ( 
    SELECT * 
    FROM VALUES (Object_List) )

SELECT 
REPLACE(REPLACE(VALUE,'{',''),'}','')
SPLITTED_VALUE 
FROM DATATABLE , 
LATERAL SPLIT_TO_TABLE (VALUEA,',') ;
    
'

;
NickW
  • 8,430
  • 2
  • 6
  • 19
Nirmal Kumar Kosuru
  • 49
  • 1
  • 7
  • Try changing the code block delimiters from a single quote to $$ – NickW Feb 02 '22 at 17:49
  • I have tried the below code changed function from sql to javascript the compile error has gone but while calling the function as below SELECT * FROM TABLE(Extract_Tables('TABLE1,TABLE2,TABLE3,TABLE4')) getting the following error: SQL Error [100131] [P0000]: JavaScript compilation error: Uncaught SyntaxError: Unexpected identifier in EXTRACT_TABLES at 'WITH DATATABLE(VALUEA) AS ( SELECT * FROM VALUES ( Object_List ) ) SELECT REPLACE(REPLACE(VALUE,'{',''),'}','') SPLITTED_VALUE FROM DATATABLE , LATERAL SPLIT_TO_TABLE (VALUEA,',') ;' position 5. – Nirmal Kumar Kosuru Feb 02 '22 at 18:07
  • Please update your question with any additional information so everything is in one place - don't use comments except for comments – NickW Feb 02 '22 at 19:00
  • I tried $$ still I get the same error as above. – Nirmal Kumar Kosuru Feb 02 '22 at 21:32
  • Does the complete SQL block run on its own i.e. outside of the function? – NickW Feb 02 '22 at 21:55

1 Answers1

0

Your embedded SQL in the function should not have a ; in it.

Also you SQL can be simplified down to:

CREATE OR REPLACE FUNCTION Extract_TableNames(Object_List TEXT)
    RETURNS TABLE (TABLE_NAME VARCHAR)
AS $$
SELECT 
    REPLACE(REPLACE(s.value,'{',''),'}','') AS splitted_value 
    FROM TABLE(SPLIT_TO_TABLE (object_list,',')) s
$$;

which can be used as:

SELECT * FROM TABLE(Extract_TableNames('{aa},{bb}'));

which gives:

TABLE_NAME
aa
bb

showing the SQL working by itself outside of the function:

SELECT 
    REPLACE(REPLACE(s.value,'{',''),'}','') AS splitted_value 
    FROM TABLE(SPLIT_TO_TABLE ('{aa},{bb}',',')) s;
TABLE_NAME
aa
bb
Simeon Pilgrim
  • 22,906
  • 3
  • 32
  • 45