How to add comment in PostgreSQL using a variable or a computed expression?

Question

I want to add a comment on a PostgreSQL table my_data in pure SQL containing the current date.

I tried to use concatenate operator but it does not work:

COMMENT ON TABLE my_data
    IS 'Last update: ' || CURRENT_DATE::text;

I tried also with a variable but I have the same issue:

DO $$
DECLARE comm text;
BEGIN
    SELECT 'Last update: ' || CURRENT_DATE::text INTO comm;
    COMMENT ON TABLE my_data IS comm;
END $$;

I don't see anything related to my issue in PG documentation. Is there a way to achieve it in pure SQL ?

Answer 1

Try with an anonymous code block. Format your SQL Statement with format() and then EXECUTE it:

DO $$
BEGIN    
 EXECUTE format('COMMENT ON TABLE my_data IS %L','Last update: '||CURRENT_DATE);
END $$;