高斯数据库[GaussDB]TPDSS下面执行批量脚本报错。

概要

在TPDSS下面执行脚本的时候，发现报错，错误码是：42601，说是在预编译的环境下，尝试将一个包含多个SQL命令的字符串作为预编译语句执行时失败了。

错误代码：[0]SQL错误码: = 42601 [x.x.x.x:62503/x.x.x.x:8123] ERROR: cannot insert multiple commands into a prepared statement

这么神奇的报错，最后居然是"/"给了结啦。

• 报错示例：

--相关上传文件的表结构调整
---KISC_IMG_BIZ_COMPARE 影像内容检测
---增加对象存储名
do $$
declare   
  table_name1 varchar := 'kisc_img_biz_compare';  
  column_name1 varchar := 'object_storage_id';   
  exists_check boolean;  
begin   
  -- 检查列是否存在  
  select exists (select 1 from information_schema.columns where table_name = table_name1 and column_name = column_name1) into exists_check;
  -- 如果列不存在，则添加列  
  if not exists_check then  
    execute 'alter table ' || table_name1 || '  add column ' || column_name1 || ' varchar(1024) null';   
  end if;  
end $$;

---增加对象存储桶
do $$
declare   
  table_name1 varchar := 'kisc_img_biz_compare';  
  column_name1 varchar := 'object_storage_policy';   
  exists_check boolean;  
begin   
  -- 检查列是否存在  
  select exists (select 1 from information_schema.columns where table_name = table_name1 and column_name = column_name1) into exists_check;
  -- 如果列不存在，则添加列  
  if not exists_check then  
    execute 'alter table ' || table_name1 || '  add column ' || column_name1 || ' varchar(128) null';   
  end if;  
end $$;

• 正确示例

--相关上传文件的表结构调整
---KISC_IMG_BIZ_COMPARE 影像内容检测
---增加对象存储名
do $$
declare   
  table_name1 varchar := 'kisc_img_biz_compare';  
  column_name1 varchar := 'object_storage_id';   
  exists_check boolean;  
begin   
  -- 检查列是否存在  
  select exists (select 1 from information_schema.columns where table_name = table_name1 and column_name = column_name1) into exists_check;
  -- 如果列不存在，则添加列  
  if not exists_check then  
    execute 'alter table ' || table_name1 || 'add column' || column_name1 || ' varchar(1024) null';   
  end if;  
end $$;
/

---增加对象存储桶
do $$
declare   
  table_name1 varchar := 'kisc_img_biz_compare';  
  column_name1 varchar := 'object_storage_policy';   
  exists_check boolean;  
begin   
  -- 检查列是否存在  
  select exists (select 1 from information_schema.columns where table_name = table_name1 and column_name = column_name1) into exists_check;
  -- 如果列不存在，则添加列  
  if not exists_check then  
    execute 'alter table ' || table_name1 || 'add column' || column_name1 || ' varchar(128) null';   
  end if;  
end $$;
/

---

PostgreSQL 对表添加字段

直接上案例，不废话。

• 示例1：

do $$
declare   
  table_name1 varchar := 'kisc_img_biz_compare';  
  column_name1 varchar := 'object_storage_id';   
  exists_check boolean;  
begin   
  -- 检查列是否存在  
  select exists (select 1 from information_schema.columns where table_name = table_name1 and column_name = column_name1) into exists_check;
  -- 如果列不存在，则添加列  
  if not exists_check then  
    execute 'alter table ' || table_name1 || '  add column ' || column_name1 || ' varchar(1024) null';   
  end if;  
end $$;

• 示例2：

CREATE OR REPLACE FUNCTION add_column_if_not_exists(tablename text, columnname text, columntype text)  
RETURNS void AS 
$$
  
DECLARE  
    col_exists boolean;  
BEGIN  
    -- 检查字段是否已存在  
    SELECT EXISTS (  
        SELECT 1  
        FROM INFORMATION_SCHEMA.COLUMNS  
        WHERE TABLE_NAME = tablename  
          AND COLUMN_NAME = columnname  
    ) INTO col_exists;  
  
    -- 如果字段不存在，则添加字段  
    IF NOT col_exists THEN  
        EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s', tablename, columnname, columntype);  
        RAISE NOTICE 'Column % added to table %.', columnname, tablename;  
    ELSE  
        RAISE NOTICE 'Column % already exists in table %.', columnname, tablename;  
    END IF;  
EXCEPTION  
    WHEN OTHERS THEN  
        RAISE EXCEPTION 'An error occurred while adding column % to table %: %', columnname, tablename, SQLERRM;  
END;  

$$
 LANGUAGE plpgsql;

--调用
SELECT add_column_if_not_exists('my_table', 'new_column', 'varchar(100)');

---

小结

今天就总结到这里了，后面有更新再写。