CODEWITHSUNDEEP
sqlschemavertica
Shieldsy
Shieldsy

Reputation: 11

Run the same SQL query for multiple schemas

I am hoping to write one SQL query that goes through all 20+ schemas without the need to constantly replace the search_path. I've tried UNION ALL but in most situations separating the query might take all the time I saved by not hard writing schemas. The query itself can be very basic such as:

SELECT *FROM schm1.table1

UNION ALL

SELECT *FROM schm2.table1

Thank you for your assistance!

Upvotes: 1

Views: 2564

Answers (1)

marcothesane
marcothesane

Reputation: 6749

"The impossible will be completed as you wait; please allow two days for the delivery of miracles".

I'm afraid what you want to achieve can only be done by SQL generating SQL:

SELECT
  CASE ROW_NUMBER() OVER(ORDER BY table_schema)
    WHEN 1 THEN ''
    ELSE 'UNION ALL '
  END
||'SELECT * FROM '
||table_schema
||'.'
||table_name
||  CASE ROW_NUMBER() OVER(ORDER BY table_schema DESC)
    WHEN 1 THEN ';'
    ELSE CHR(10)
  END
FROM tables
WHERE table_name='d_teas_scd'
ORDER BY table_schema
;

What I get with d_teas_scd as table_name, is this:

SELECT * FROM flatt.d_teas_scd
UNION ALL SELECT * FROM public.d_teas_scd
UNION ALL SELECT * FROM star.d_teas_scd;

It can't guarantee that all tables with the same name have the same structure, though, that's why the resulting query could fail - that's your responsibility...

Happy playing

Marco the Sane

Upvotes: 3

Related Questions