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 ?

Jim Jones · Accepted Answer · 2021-09-28 11:13:01Z

4

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

edited Sep 28, 2021 at 11:13

answered Sep 28, 2021 at 11:07

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gurmokh · Accepted Answer · 2021-09-28 10:29:18Z

3

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;

answered Sep 28, 2021 at 10:29

Gurmokh

2,09127 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related