Generating a single result set from multiple tables dynamically in Snowflake

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.

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;