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

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 ?

CodePudding user response：

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 $$;

CodePudding user response：

This is already answered on DB stack exchange. https://dba.stackexchange.com/questions/119291/how-to-create-a-dynamic-comment-to-an-object

create or replace function comment_on(p_type text, p_object text, 
p_comment text)
returns text
as
$$
declare 
  l_sql     text;
  l_comment text;
begin
  l_comment := replace(p_comment, '${created}', 'created at 
'||to_char(current_date, 'yyyy-mm-dd'));
  l_sql := 'comment on '||p_type||' '||p_object||' is 
'||quote_literal(l_comment);
  execute l_sql;
  return l_comment;
end;
$$
language plpgsql;