postgresql 遍历表以从Postgres中的创建日期列检索最大日期

yvfmudvl 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(2)|浏览(230)

我需要使用一组表的最新创建日期来监视正在进行的数据流。

SELECT MAX(z_date_creation)  
FROM table_schema.table_name

在一组表上检索

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%'

然后将其提供给一个专用的"ods.dates_derniere_maj"表，我将在该表上插入报告。
我使用游标是为了更好地遍历需要从中获取MAX(z_date_creation)的表。我设法将table_schema和table_name值馈送到ods.dates_derniere_maj表中，但无法找到从这些表中获取MAX(z_date_creation)的方法。
我被嵌套查询部分卡住了。
以下是我目前的想法：

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 $$;`

我尝试过直接在FROM子句中传递table_rec. table_schema和table_rec. table_name变量，但没有成功，所以我尝试事先将它们连接起来。
任何帮助将不胜感激!
非常感谢!
弗兰克

postgresql

来源：https://stackoverflow.com/questions/75188169/looping-through-tables-to-retrieve-max-dates-from-a-created-date-column-in-postg

2条答案

按热度按时间

nqwrtyyt1#

试试这样的方法：

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 ;
$$ ;

参见dbfiddle中的测试结果

赞(0）回复(0）举报 2023-01-25

tzdcorbm2#

以下是我在爱德华的宝贵帮助下得出的结论！

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 $$;

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql 遍历表以从Postgres中的创建日期列检索最大日期

2条答案

相关问题

热门标签

最新问答