Get latest timestamp from column in given table dynamically

Question

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:

Query used:

select max(updated) from mytable;

Answer 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:

• Table name as a PostgreSQL function parameter
• Find tables with columns with empty and NULL values in Postgresql
• Check whether empty strings are present in character-type columns