在表的注释中添加时间戳

Question

我经常需要在PostgreSQL（9.4.1）中重新创建一张表，类似于这样：

DROP TABLE IF EXISTS test.foo;
CREATE TABLE test.foo AS
  SELECT * FROM test.dagi_kommune
  WHERE ST_Area(wkb_geometry) < 500;

我想在表格中添加一个注释，说明表格创建的时间。创建基本注释没有问题，例如：

COMMENT ON TABLE test.foo IS 'Table create date: ';

我可以生成一个独立的时间戳，像这样:

SELECT to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS');

但是，如果我尝试将时间戳放入注释中，就像这样：

COMMENT ON TABLE test.foo IS to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS');

我得到了以下响应：

ERROR:  syntax error at or near "to_char"
LINE 10: COMMENT ON TABLE test.foo IS to_char(LOCALTIMESTAMP, 'YYYY-M...
                                  ^

********** Error **********

ERROR: syntax error at or near "to_char"
SQL state: 42601
Character: 276

如何将当前的“日期和时间”添加到表格注释中？

- Martin

Answer 1

您需要构建并执行动态 SQL 语句。

DO
$do$
BEGIN
EXECUTE 'COMMENT ON TABLE b2 IS ''Table create date: '
     || to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS')
     || '''';
END
$do$

在这种情况下，普通的连接是安全的。对于不安全的输入，请使用format()。请参见以下链接：

• Error when setting n_distinct using a plpgsql variable
• Table name as a PostgreSQL function parameter
• To convert from Python arrays to PostgreSQL quickly?

可选换行符。同样适用于一行脚本。

DO $do$BEGIN EXECUTE 'COMMENT ON TABLE b2 IS ''Table create date: ' || to_char(LOCALTIMESTAMP, 'YYYY-MM-DD HH:MI:SS') || ''''; END $do$;

- Erwin Brandstetter

---

谢谢。对于我想要的用途来说，它有点笨拙，就像使用了太多行一样。这将会大大混乱我的SQL脚本。但现在我知道该如何做了。 - Martin

Answer 2

如果您将使用 psql 执行命令，则以下是使用 psql 变量的清晰简洁方法：

SELECT current_timestamp AS now
\gset
COMMENT ON TABLE test.foo IS :'now';

- Spencer Mathews

---

谢谢您的回复，但是这个项目已经几年前就完成了... - Martin

明白了。这更多是为那些未来到达这里的人准备的。 - Spencer Mathews

我可以将其与其他文本连接起来吗？比如“当前时间是：_variable_”。 - kerfuffle

Answer 3

强化Spencer上面的评论：

SELECT current_timestamp as dump_time
\gset
COMMENT ON SCHEMA my_schema IS 'Latest dump at: ':'dump_time';

- kerfuffle