Postgresql IF 块中的 CREATE FUNCTION 语句抛出错误，同时运行它自己的作品

错误的直接原因是不正确的美元报价.这将会奏效:

DO
$do$
BEGIN
   IF NOT EXISTS (SELECT * FROM test_table WHERE character_length(col_a) > 100) THEN
      ALTER TABLE test_table ALTER COLUMN col_a TYPE varchar(100);

      DROP FUNCTION IF EXISTS test_function(varchar(100));

      CREATE OR REPLACE FUNCTION test_function(test_param varchar(100))
        RETURNS TABLE (
         id uuid,
         col_a varchar(100),
         col_b int
        )
      LANGUAGE plpgsql AS
      $func$
      BEGIN
         RETURN QUERY
         SELECT test_table.id, test_table.col_a, test_table.col_b
         FROM   test_table
         WHERE  test_table.col_a = test_param;
      END
      $func$;
   ELSE
      RAISE NOTICE 'Cannot Revert. Rows exist that would be truncated!';
   END IF;
END
$do$;

请参见:

• Insert text with single quotes in PostgreSQL个
• What are '$$' used for in PL/pgSQL

But I wouldn't do most of what you are doing there to begin with. Use the data type text in table and function and be done with it. 请参见:

• Any downsides of using data type "text" for storing strings?个
• Should I add an arbitrary length limit to VARCHAR columns?个

如果您确实需要对最大字符数进行限制，仍然可以考虑text并添加CHECK个限制.

ALTER TABLE test_table ADD CONSTRAINT test_table_col_a_maxlen_200 CHECK (length(col_a) < 201);

然后，如果以后要更改该约束，只需执行以下操作:

ALTER TABLE test_table
  DROP CONSTRAINT test_table_col_a_maxlen_200  -- or whatever it was
, ADD  CONSTRAINT test_table_col_a_maxlen_100 CHECK (length(col_a) < 101);

Postgres将自动为您验证CHECK约束，如果有任何行违反该约束，则会失败并返回错误:

错误:某行违反了关系"test_table"的判断约束"test_table_ol_a_Maxlen_100"

In fairness，在现代的Postgres中，您也可以将更改应用到varchar(n).如果有任何现有行太长，它将进行判断并失败:

错误:类型字符变化的值太长(100)

因此，即使你坚持varchar(n)个，你也可以简化事情.