Get latest timestamp from column in given table dynamically

All we need is an easy explanation of the problem, so here it is.

I want to get last timestmap from a table with the column updated in PostgreSQL.

And I want a function to do that. So I can pass the table name as parameter and the function returns the latest timestamp in column updated.

My table:
Get latest timestamp from column in given table dynamically

Query used:

select max(updated) from mytable;

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Probably simplest with regclass as input and an OUT parameter:

CREATE OR REPLACE FUNCTION f_last_update(_tbl regclass, OUT _last_update timestamp)
  LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE 'SELECT max(updated_at) FROM ' || _tbl
   INTO _last_update;
END
$func$;

Call:

SELECT f_last_update('mytable');

Optionally schema-qualified and double-quoted where required:

SELECT f_last_update('"My_odd_ScHeMa"."Unwise table name"');

Safe against SQL injection because regclass is automatically quoted and schema-qualified properly when converted to text.

See:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply