How to create comment on table from query result

Question

I want to use a query result as comment on the table. I have tried in psql:

\set list select count(*) from department.master_data
COMMENT ON TABLE department.master_data IS :list;

But I got this error:

it=> \set list select count(*) from department.master_data
it=> COMMENT ON TABLE department.master_data IS :list;
ERROR:  syntax error at or near "selectcount"
LINE 1: ...ABLE department.master_data IS selectcoun...

Answer 1

While I am not sure what you want to achieve (why it makes sense to store a count as a comment, especially if your table will get new rows (or lose some to DELETEs)), but here is how what your problem is and how you can solve it.

First, check what psql thinks you have in the variable:

\echo :list
selectcount(*)fromdepartment.master_data

Clearly not the result of your query. To store that, you'll have to use \gset:

> SELECT count(*) AS master_data_count FROM department.master_data;
 master_data_count
───────────────────
        1234567890
(1 row)

> \gset
> \echo :master_data_count
1234567890

If you would try to set the table description now, you'd get another error:

ERROR:  syntax error at or near "1234567890"
LINE 1: ...ABLE department.master_data IS selectcoun...

This is because the comment must be a (quote-delimited) text. To get one out of your variable, you have two options. One is adding escaped quotes to the variable using psql (using the same feature you misused setting :list:

> \set master_data_count '\'' :master_data_count '\''
> \echo :master_data_count
'1234567890'

Or you can add the quote already in your query, so SELECT count(*) turns into

SELECT $$'$$ || count(*) || $$'$$

(I used dollar quoting above to avoid typing things like ''''.)

The result you can already use as a comment, as it contains the necessary quotes.

Answer 2

Use \gset in psql to store the result of a query in a local variable - like dezso already advised. Shorter yet, \gset can replace the semicolon (;) as query terminator:

test=# SELECT count(*) AS list FROM department.master_data \gset

Then, to get a single-quoted string (with any contained single quotes safely escaped automatically) refer to the psql variable with this syntax variant: :'list'. Note the enclosing '' after the colon!

test=# COMMENT ON TABLE department.master_data IS :'list';

Read about SQL interpolation in the manual.

Related:

• CREATE SEQUENCE using expressions with psql variables for parameters

Answer 3

According to the manual, the :list part should be enclosed in quotation marks:

COMMENT ON TABLE  department.master_data
    IS ':list';