postgresql批量插入数据脚本_PGSQL常用脚本

/*

scheme.test_table、scheme.test_table1、scheme.test_table2表字段(id,name,age);

raise用于函数中打印输出，类似于oracle的dbms_output.putline();

raise的语法为: raise notice 'test raise content %',param;

上面语句中的

%为参数占位符,有多个参数时就添加多个%,不用考虑参数的数值类型;

notice字段为级别，可以为debug/log/info/notice/warning/exception,

这些级别的信息是直接写到服务端日志还是返回到客户端或是二者皆有,是由log_min_messages和client_min_messages两个参数控制,这两个参数在数据库初始化时用到。

*/CREATE OR REPLACE FUNCTION scheme.myfunction() RETURNS INTEGER AS $$

DECLARE a_record record;

records CURSOR FOR SELECT * FROM scheme.test_table;

v_sql VARCHAR ( 4000 );

affect INT;

BEGIN

OPEN records;

loop

FETCH records INTO a_record;

exit WHEN NOT FOUND;

raise notice'fechting idm_id-%',a_record.ID;

-- update name

v_sql = 'update scheme.test_table1 set name = (select name from scheme.test_table where id = $1),update_user=$2 where id = $3';

EXECUTE v_sql USING a_record.id, 'sys_admin', a_record.id;

GET DIAGNOSTICS affect = ROW_COUNT;

raise notice'update name-%',affect;

-- update age

v_sql = 'update scheme.test_table2 set age = (select age from scheme.test_table where id = $1 ),update_user=$2 where id = $3';

EXECUTE v_sql USING a_record.ID, 'sys_admin', a_record.ID;

GET DIAGNOSTICS affect = ROW_COUNT;

raise notice'update age-%', affect;

END loop;

CLOSE records;

RETURN affect;

END;

$$ LANGUAGE plpgsql;

-- 执行函数，进行数据插入

SELECT scheme.myfunction();

-- 删除函数

DROP FUNCTION scheme.myfunction();