Looping through tables to retrieve max dates from a created date column in Postgres-CodePudding

I need to monitor the ongoing flow of data using the latest created date for a set of tables. Basically, I need to batch run

SELECT MAX(z_date_creation)  
FROM table_schema.table_name

on a set of tables I retrieve with

SELECT  
  c.table_schema,  
  c.table_name  
FROM information_schema."columns" c  
WHERE c.column_name LIKE '%z_date_creation'  
AND c.table_schema = 'datawarehouse'  
AND c.table_name NOT LIKE 'partition%'

and then feed it to a dedicated "ods.dates_derniere_maj" table I'll be pluging reports on.

I am using a cursor for like of a better idea to iterate through the tables in need to get the MAX(z_date_creation) from. I manage to feed the table_schema and table_name values into my ods.dates_derniere_maj table but cannot find a way to also get the MAX(z_date_creation) from these tables.

I'm stuck with the nested query part.

Here is what I've come up with so far :

DO $$  
DECLARE  
    table_rec record ;  
    max_date TEXT DEFAULT NOW();  
    cursor1 CURSOR FOR  
      SELECT DISTINCT c.table_schema, c.table_name, c.column_name  
      FROM information_schema."columns" c  
      WHERE c.table_schema = 'datawarehouse'  
      AND c.table_name NOT LIKE 'partition%'  
      AND c.column_name LIKE '%creation%';  
    from_clause TEXT;  
    date_column TEXT;  
BEGIN  
  FOR table_rec IN cursor1
  LOOP  
  from_clause := CONCAT(table_rec.table_schema, '.', table_rec.table_name);  
  date_column := CONCAT(table_rec.table_schema, '.', table_rec.table_name,'.','z_date_creation');  

Which code herebelow ?

PREPARE nom_req (text, text) AS  
  SELECT MAX($1) FROM $2 ; ---> not working, syntax error on $2  
max_date := EXECUTE nom_req (date_column, from_clause) ; ---> not working  
  SELECT MAX(date_column) INTO max_date FROM CONCAT(from_clause) ; ----> not working  
 
INSERT INTO ods.dates_derniere_maj (schema_name, table_name, z_date_creation_max)  
    VALUES (table_rec.table_schema, table_rec.table_name, max_date);  
END LOOP;  
END $$;`

I have tried passing the table_rec.table_schema and table_rec.table_name variables directly in the FROM clause but that didn't work so I tried to concatenate them beforehand.

Any help will be much appreciated !

Thanks a bunch !

Franck

CodePudding user response：

Try something like this :

CREATE FUNCTION max_date() RETURNS date LANGUAGE plpgsql AS
$$
DECLARE
  table_rec record ;
  max_date date ;
  result date ;
  cursor1 CURSOR FOR  
      SELECT DISTINCT c.table_schema, c.table_name, c.column_name  
      FROM information_schema."columns" c  
      WHERE c.table_schema = 'datawarehouse'  
      AND c.table_name NOT LIKE 'partition%'  
      AND c.column_name LIKE '%creation%';  
BEGIN
  FOR table_rec IN cursor1
  LOOP  
    EXECUTE FORMAT( 'SELECT max(%I) FROM %I.%I'
                  , table_rec.column_name
                  , table_rec.table_schema
                  , table_rec.table_name
                  ) 
    INTO max_date ;
    result = greatest(result, max_date) ;
  END LOOP ;
  RETURN result ;
END ;
$$ ;

see test result in dbfiddle

CodePudding user response：

Here is what I came up with with Edouard's invaluable help !

DO $$ 
DECLARE
    table_rec record ;
    max_date TEXT DEFAULT NOW();
    cursor1 CURSOR FOR SELECT DISTINCT c.table_schema, c.table_name, c.column_name
            FROM information_schema."columns" c
            WHERE c.table_schema = 'datawarehouse'
            AND c.table_name NOT LIKE 'partition%'
            AND c.column_name LIKE '%creation%';

BEGIN 
    FOR table_rec IN cursor1
    LOOP
        EXECUTE FORMAT( 'SELECT max(%I) FROM %I.%I'
                      , table_rec.column_name
                      , table_rec.table_schema
                      , table_rec.table_name
                      ) 
        INTO max_date ;
        INSERT INTO ods.dates_derniere_maj (schema_name, table_name, z_date_creation_max)
            VALUES (table_rec.table_schema, table_rec.table_name, max_date);
    END LOOP;
END $$;