Looping through all schemas

Question

I have a SQL query like so -

with referral as 
(
select (date_trunc('week', soc_date))::date as year_week, *
from p148.referral e
join p148.contact_accounts ca on 
e.contact_account_id = ca.id
where soc_date >= '2018-01-01'
and soc_date <= '2021-09-30'
),
owner as 
(
select * from owner1 
)
select *
from 
referral ra 
join owner o 
on o."name" = ra.name

I need to iterate through all the schemas in my database and run this query and save the result of it in the public schema. So, the public schema will have 1 table that will contain the result of this query from all schemas.

Answer 1

I'd simplify the query. I don't see a need for the second CTE - or any CTE for that matter:

SELECT (date_trunc('week', e.soc_date))::date AS year_week, * -- ??
FROM   p148.referral e
JOIN   p148.contact_accounts ca ON e.contact_account_id = ca.id
JOIN   public.owner1 o USING (name)  -- don't duplicate the column "name"
WHERE  e.soc_date >= '2018-01-01'
AND    e.soc_date <= '2021-09-30'

The USING clause only outputs a single copy of the join column name. I still strongly doubt you want SELECT *. Will probably produce multiple id columns, which would have to be renamed. And it's pointless to store the effectively identical e.contact_account_id and c.id ...

Get the set of existing schemas in the database (minus system schemas you'll want to exclude):

SELECT nspname
FROM   pg_catalog.pg_namespace
WHERE  nspname NOT LIKE 'pg\_%'
AND    nspname <> 'information_schema';

Related:

• Add a column to a table in all schemas of a PostgreSQL database

Now build one copy of the query for every schema and execute dynamically. One option: with \gexec in psql:

SELECT format(
 $$INSERT INTO public.my_table (year_week, ...)
   SELECT (date_trunc('week', e.soc_date))::date AS year_week, *
   FROM   %1$I.referral e
   JOIN   %1$I.contact_accounts ca ON e.contact_account_id = ca.id
   JOIN   public.owner1 o USING (name)  -- don't duplicate the column "name"
   WHERE  e.soc_date >= '2018-01-01'
   AND    e.soc_date <= '2021-09-30'$$
 , nspname)
FROM   pg_catalog.pg_namespace
WHERE  nspname NOT LIKE 'pg\_%'
AND    nspname <> 'information_schema'\gexec

See:

• My function returned a string. How to execute it?

Use format() with the format specifier %I as demonstrated to properly quote concatenated names and defend against SQL injection. See:

• Define table and column names as arguments in a plpgsql function?

There are other options, it all depends ...