2

Is there any way to generate the text for comments by using a select statement (or otherwise to generate it dynamically)?

Background:

I have a rather big query stored in a table table_a for reusing it. In the comment for the table I've stored the creation date (and thus the date of the data status). This date I generate using the capabilities of DbVisualizer (DbVisualizer-Variables)::

comment on table table_a is 'Date: ${dbvis-date}$'

Now I use this table_a to calculate something and store it in a table table_b.

I now would like to put the comment of table_a as the comment of table_b. The comment can be obtained as described in How to retrieve the comment of a PostgreSQL database?:

select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
where relname = 'table_a'

So, if I could use a select-statement in comment, it would be an easy thing to do - but appearently it is not allowed...

--doesn't work
comment on 'table_b' is (select description from pg_description
join pg_class on pg_description.objoid = pg_class.oid
where relname = 'table_a')

I use Postgresql 9.1

Community
  • 1
  • 1
Julian
  • 741
  • 8
  • 19

1 Answers1

3

You can use EXECUTE inside an anonymous block:

DO 
$$
  DECLARE
    comment_text text;
  BEGIN
    comment_text := (
      select description 
      from pg_description 
      join pg_class on pg_description.objoid = pg_class.oid 
      where relname = 'table_a');

    EXECUTE('COMMENT ON TABLE table_b IS ''' || comment_text || '''');
  END;
$$;
siphiuel
  • 3,480
  • 4
  • 31
  • 34