In Need of an SQL Factory

Having performed hundreds of DNA engagements at primarily large customers in the world, our DNA data warehouse allows us to harvest factual knowledge regarding deployment and utilization trends inside IBM Lotus Notes & Domino environments. Once the research question properly translates into an SQL select statement, we need to apply that statement across all the customer schemas from where we wish to harvest the data.

However, each DNA engagement is unique and tailored to the specific customer. So are the corresponding tables in each customer schema. Consequently, having to select all schemas to apply our query to, is a time consuming task.

I decided to automate this process. Below I'm explaining what I did.

One way to discover which schemas we can run our queries on, is to use pg_tables:

SELECT schemaname FROM pg_tables WHERE tablename = 'user_session';

Next, I needed to craft output that in itself represents an SQL statement. (Beware to escape quotes properly):

SELECT

 '

 DROP TABLE IF EXISTS '|| schemaname ||'.template_utilization\;

 CREATE TABLE '||schemaname||'.template_utilization AS

  SELECT 

    \'' || schemaname ||'\'::text AS schemaname,

    LOWER(template_used) AS inherit_from, 

    CASE 

      WHEN template_type IS NULL THEN ''Not Classified'' 

      ELSE template_type 

    END AS template_type, 

    dt.description AS database_type, 

    COUNT(*) AS num_databases, 

    COUNT(distinct db.replica_id) AS num_replicaids

  FROM

    '||SPLIT_PART(schemaname, '_', 1) || '_dwh.notes_db_detail db

    JOIN dna.database_type dt ON dt.dbtype_id = db.dbtype_id

  WHERE

    NOT db.is_removed AND db.in_scope

  GROUP BY 

    template_used, 

    template_type, 

    dt.description\;

  '::text AS SQLStatement

FROM 

  pg_tables 

WHERE tablename = 'notes_db_detail';

Executing this query outputs a row for each schema where the table exists, where each row represents the final query we can execute against the (176!) schemas we wanted. Copy the output and paste this into a new window and press F5.