postgresql IF块中的CREATE FUNCTION语句在运行时抛出错误

lymgl2op 于 2023-05-28 发布在 PostgreSQL

关注(0)|答案(1)|浏览(179)

我是PostgreSQL的新手（目前在PostgreSQL 13上），可能会混淆我遇到的其他SQL（Microsoft SQL）。
目标是Assert表列中没有值会被截断，然后减少列长度，并对相关函数的返回类型执行相同的操作
给出错误的代码示例，因此可以复制：

/* 
 CREATE TABLE test_table (id uuid, col_a varchar(100), col_b int); 
 INSERT INTO test_table VALUES (gen_random_uuid(), 'asdf', 1);
 **/

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 $$
        BEGIN
            RETURN QUERY
            SELECT test_table.id AS id, test_table.col_a AS col_a, test_table.col_b AS col_b
            FROM test_table
            WHERE test_table.col_a = test_param;
        END;
        $$;
    ELSE
        RAISE NOTICE 'Cannot Revert. Rows exist that would be truncated!';
    END IF;
END $$

SELECT id, col_a, col_b FROM test_function ('asdf');

/* DROP TABLE test_table */

我得到的错误是：
SQL错误[42601]：错误：在“开始”位置或其附近出现语法错误：400
我尝试过动态代码，我自己运行了函数语句--进行了健全性检查;事实上，如果没有IF块，一起或单独运行每个语句也可以正常工作。
我的方法有什么问题，如何解决？

postgresql

来源：https://stackoverflow.com/questions/76344627/create-function-statement-in-an-if-block-throws-error-while-running-on-its-own

1条答案

按热度按时间

mfuanj7w1#

错误的直接原因是不正确的美元报价。这将工作：

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$;

参见：

在PostgreSQL中插入带单引号的文本
PL/pgSQL中的“$$”是什么
*********************************************************************************************************在表和函数中使用数据类型text并完成它。参见：
使用数据类型“text”存储字符串有什么缺点吗？
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_col_a_maxlen_100”

公平地说，您也可以将更改应用到现代Postgres中的varchar(n)。如果任何现有行太长，它将检查并失败：

错误：类型字符变化的值太长（100）
因此，即使您坚持使用varchar(n)，也可以简化操作。

赞(0）回复(0）举报 2023-05-28

我来回答

postgresql IF块中的CREATE FUNCTION语句在运行时抛出错误

1条答案

相关问题

热门标签

最新问答