I am trying to get a result set with 2 columns from snowflake using dynamic SQL, this is read from the INFORMATION_SCHEMA_TABLES metadata.
I simply want the (TABLE_NAME, PUB_DATE) columns for all tables in the Schema, but I can’t get anything to work, I do not have access to modify the schema so I cannot use Stored Procedures or create temporary tables.
DECLARE
resultItems ARRAY;
table_names ARRAY;
table_name STRING;
min_date TIMESTAMP;
BEGIN
SELECT ARRAY_AGG(TABLE_NAME) INTO table_names
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'MY_SCHEMA';
resultItems := ARRAY_CONSTRUCT();
FOR i IN 1 : ARRAY_RANGE(1, ARRAY_SIZE(table_names)) LOOP
table_name := table_names[i];
EXECUTE IMMEDIATE
'SELECT MIN(pub_date) INTO :min_date FROM MY_SCHEMA.'||table_name||';';
resultItems := ARRAY_APPEND(resultItems, OBJECT_CONSTRUCT(
'table_name', table_name, 'pub_date', min_date));
END LOOP;
END;
The compiler gives me very obscure error messages when changing things so I am a bit lost now.
Any help would be appreciated, and also if someone could show me how to select all the results that would be great also.
7
This should do roughly what you want:
DECLARE
res1 RESULTSET DEFAULT (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'MY_SCHEMA' );
cur1 CURSOR FOR res1;
sql_stmnt STRING DEFAULT '';
res2 RESULTSET;
BEGIN
FOR rec in cur1 DO
IF (sql_stmnt = '') THEN
sql_stmnt := 'SELECT ''||rec.table_name||'', MIN(pub_date) FROM MY_SCHEMA.'||rec.table_name;
ELSE
sql_stmnt := sql_stmnt||' UNION SELECT ''||rec.table_name||'', MIN(pub_date) FROM MY_SCHEMA.'||rec.table_name;
END IF;
END FOR;
res2 := (EXECUTE IMMEDIATE :sql_stmnt);
RETURN TABLE(res2);
END;