Get all distinct values of specific column in all tables in a given schema (Postgres)
You can use query_to_xml() to dynamically run a SQL statement and get the result as an XML string. Using XMLTABLE this can then be converted back into rows and columns
with queries as (    
  select format('select distinct %L as table_name, %I as col from %I.%I', c.table_name, c.column_name, c.table_schema, c.table_name) as sql
  from information_schema.columns c
  where c.table_schema = 'public'
    and c.column_name = 'query_me'
), results as (
  select query_to_xml(q.sql, true, false, '')  as data
  from queries q
)
select x.*
from results r
  cross join xmltable('/table/row' passing r.data
                       columns table_name text path 'table_name', 
                               value      text path 'col') as x
order by x.table_name

The first CTE returns something like this:
select distinct 'sql_test_1' as table_name, query_me as col from public.sql_test_1
select distinct 'sql_test_2' as table_name, query_me as col from public.sql_test_2

The second CTE then runs each of those queries and returns the result as XML. Something like this:
&lt;table xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;&gt;
&lt;row&gt;
  &lt;table_name&gt;sql_test_1&lt;/table_name&gt;
  &lt;col&gt;c&lt;/col&gt;
&lt;/row&gt;

&lt;row&gt;
  &lt;table_name&gt;sql_test_1&lt;/table_name&gt;
  &lt;col&gt;a&lt;/col&gt;
&lt;/row&gt;

&lt;row&gt;
  &lt;table_name&gt;sql_test_1&lt;/table_name&gt;
  &lt;col&gt;b&lt;/col&gt;
&lt;/row&gt;

&lt;/table&gt;

And the final step then extracts the values from the generated XML and returns this based on your sample data:
table_name | value
-----------+------
sql_test_1 | c    
sql_test_1 | a    
sql_test_1 | b    
sql_test_2 | g    
sql_test_2 | f    
sql_test_2 | a    

Online example