PostgreSQL:如果表上存在注释

Question

如果存在，我有点被任务困住了。

我用 COMMENTS ON TABLE 函数查询了几行

COMMENT ON TABLE my_table1 IS 'Comment for table1';
COMMENT ON TABLE my_table2 IS 'Comment for table2';
COMMENT ON TABLE my_table3 IS 'Comment for table3';
COMMENT ON TABLE my_table3 IS 'Comment for table4';

我想做的是，当我执行此查询时，如果其中一个表不存在，查询将传递该查询并继续。

我尝试使用 IF EXIST 操作完成该任务，但它不像 UPDATE 或其他操作那样工作。我哪里错了？

IF EXISTS (SELECT relname FROM pg_class where relname='my_table1')
then
COMMENT ON TABLE my_table1 IS 'Comment for table1';
IF EXISTS (SELECT relname FROM pg_class where relname='my_table2')
then
COMMENT ON TABLE my_table1 IS 'Comment for table2';
IF EXISTS (SELECT relname FROM pg_class where relname='my_table3')
then
COMMENT ON TABLE my_table1 IS 'Comment for table3';
IF EXISTS (SELECT relname FROM pg_class where relname='my_table4')
then
COMMENT ON TABLE my_table1 IS 'Comment for table4';

Answer 1

你可以使用:

DO
$do$
BEGIN
  IF EXISTS (SELECT relname FROM pg_class where relname='my_table1') THEN
    COMMENT ON TABLE my_table1 IS 'Comment for table1';
  END IF;

  IF EXISTS (SELECT relname FROM pg_class where relname='my_table2') THEN
    COMMENT ON TABLE my_table1 IS 'Comment for table2';
  END IF;
END
$do$

并检查:

SELECT relname, obj_description(oid) 
FROM pg_class 
WHERE relname LIKE 'my_table%';

DBFiddle Demo