I have list of tables that have specific column names like
SELECT table_name
FROM information_schema.columns
WHERE column_name = 'column1'
I need to find the max value of column1 for each tables. I expect result like the following
|--------|--------------|
| Table | Max column1 |
|--------|--------------|
| Table1 | 100 |
| Table2 | 200 |
| ... | ... |
|--------|--------------|
How can I construct a query?
You can use a variation of the row count for all tables approach:
select t.table_name,
(xpath('/row/max/text()', xmax))[1]::text::int
from (
SELECT table_name, data_type,
query_to_xml(format('select max(%I) from %I.%I', column_name, table_schema, table_name), true, true, '') as xmax
FROM information_schema.columns
WHERE column_name = 'column1'
and table_schema = 'public'
) as t;
query_to_xml() runs a select max(..) from .. for each column returned from the query. The result of that is something like:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<max>42</max>
</row>
The xpath() function is then used to extract the value from the XML. The derived table (sub-query) is not really needed, but makes the xpath() expression more readable (in my opinion).
You may create a generic function that returns a TABLE type by constructing a UNION ALL query from information_schema.columns
CREATE OR REPLACE FUNCTION public.get_max(TEXT )
RETURNS TABLE(t_table_name TEXT, t_max text )
LANGUAGE plpgsql
AS $BODY$
DECLARE
p_colname TEXT := $1;
v_sql_statement TEXT;
BEGIN
SELECT STRING_AGG( 'SELECT '''||table_name||''','||' MAX('
|| column_name||'::text'
|| ') FROM '
|| table_name
,' UNION ALL ' ) INTO v_sql_statement
FROM information_schema.columns
WHERE column_name = p_colname
--and table_schema = 'public';
IF v_sql_statement IS NOT NULL THEN
RETURN QUERY EXECUTE v_sql_statement;
END IF;
END
$BODY$;
Execute and get the results like this.
knayak=# select * FROM get_max('id');
t_table_name | t_max
--------------+-------
f | 2
t2 | 1
friends | id4
person | 2
customer |
employe |
diary | 4
jsontable | 6
atable |
t_json | 2
ingredients | 1
test | 2
accts |
mytable | 30
(14 rows)
