es.davy.ai

Preguntas y respuestas de programación confiables

¿Tienes una pregunta?

Si tienes alguna pregunta, puedes hacerla a continuación o ingresar lo que estás buscando.

  • es.davy.ai/
  • Programación /
  • Consulta dinámica de PostgreSQL: encontrar la marca de tiempo más reciente de varias tablas no relacionadas.

Consulta dinámica de PostgreSQL: encontrar la marca de tiempo más reciente de varias tablas no relacionadas.

03 Jun, 2023 Programación

Tengo una serie de tablas, y muchas de ellas tienen una columna de timestamp. Puedo obtener una lista de todas las tablas con una columna de timestamp de la siguiente manera:

SELECT table_name 
FROM information_schema.columns 
WHERE table_schema='my_schema' AND column_name='timestamp';

table_name

apples
bananas
sharks
lemons


<p>Puedo obtener el timestamp más reciente en la tabla de "sharks" de la siguiente manera </p>

SELECT MAX(timestamp) FROM sharks;


<hr>


<h2>           max</h2>


<p>2021-11-24 00:00:00.000

```</p>


Me gustaría obtener una tabla como la siguiente


</code></pre>


table_name   |   last_updated

-------------+-------------------------

apples        | 2021-11-23 00:02:00.000

bananas       | 2019-10-16 00:04:00.000

sharks        | 2021-11-24 00:00:00.000

lemons        | 1970-01-03 10:00:00.000


<pre><code><br />Sospecho que esto requiere SQL dinámico, así que estoy intentando algo como lo siguiente


</code></pre>


SELECT (

    EXECUTE 'SELECT MAX(timestamp) FROM my_schema.' || table_name

) FROM (

    SELECT table_name

    FROM information_schema.columns

    WHERE table_schema='my_schema' AND column_name='timestamp'

);

```


Pero parece que EXECUTE no funciona en subconsultas.


El rendimiento no es una preocupación particular, solo producir los resultados deseados.
Tags:  , , ,

Answer

  1. Avatar for davy.ai
    davy.ai

    Para lograr el resultado deseado, se puede utilizar SQL dinámico con un bucle para iterar sobre cada tabla en el esquema y obtener el valor de marca de tiempo máximo. Aquí hay una consulta de ejemplo que debería funcionar:

    CREATE OR REPLACE FUNCTION get_last_updated()
RETURNS TABLE (table_name text, last_updated timestamp) AS $$

DECLARE
  table_name text;
  max_ts timestamp;
BEGIN
  -- Iterar sobre cada tabla con columna de marca de tiempo
  FOR table_name IN (
    SELECT table_name
    FROM information_schema.columns
    WHERE table_schema = 'my_schema' AND column_name = 'timestamp'
  ) LOOP
    -- Ejecutar SQL dinámico para obtener la marca de tiempo máxima para la tabla actual
    EXECUTE format('SELECT MAX(timestamp) FROM my_schema.%I', table_name)
    INTO max_ts;

    -- Insertar nombre de la tabla y marca de tiempo máxima en la tabla de resultados
    RETURN NEXT (table_name, max_ts);
  END LOOP;

END;
$$ LANGUAGE plpgsql;

    Luego puede llamar a la función para obtener el resultado deseado:

    SELECT * FROM get_last_updated();

    Esto debería devolver una tabla con las columnas table_name y last_updated, con el valor de marca de tiempo máximo para cada tabla en el esquema my_schema que tenga una columna timestamp.

Comments are closed.