I want to count certain values in all tables of a schema that contain a column that can contain those values.
Was hoping to use a LATERAL join to loop over all tables, but I’m running into issues:
select
fully_qualified_table_name,
cnt
from (
select
'datastore.' || table_name as fully_qualified_table_name
from
information_schema.columns
where
table_schema = 'datastore'
and column_name = 'dss_current_flag'
cross join lateral
select
count(*) as cnt
from
information_schema.fully_qualified_table_name
);
Is this possible?
2
Answers
Based on the answer by @jim-jones my final solution was
I’m afraid it is not possible to run dynamic queries using pure SQL. You might wanna check PL/pgSQL instead, e.g.
The more flexible approach would be to provide schema and table names as parameters in the function call instead of hard coding it in the function body, such as
CREATE FUNCTION count_records(_schema_name text, _table_name text) ..
, or even the fully qualified table name as a single parameter:CREATE FUNCTION count_records(_qualified_table_name text) ...
.Demo:
db<>fiddle