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
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.
Probably simplest with
regclass as input and an
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$;
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
- 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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂