一、pgsql中批量插入数据
INSERT INTO "scheme"."table" ("uuid", "name", "create_time", "age")
select replace(public.uuid_generate_v4() || '', '-', ''),'张三'||generate_series(1,5), clock_timestamp(),(FLOOR(RANDOM()*60)+1)||'';
二、pgsql中时间格式化
select to_char(table.create_time, 'yyyy-MM-dd HH24:MI:ss')
三、pgsql查询用户最近一次修改记录语句
SELECT *
FROM
test_table a
WHERE
NOT EXISTS (
SELECT 1
FROM test_table b
WHERE
a.uid = b.uid
AND COALESCE (b.update_time,to_date('1997-01-01 00:00:00', 'yyyy-MM-dd H24:MI:ss' ))
> COALESCE (a.update_time, to_date('1997-01-01 00:00:00', 'yyyy-MM-dd HH24:MI:ss' ))
)
四、函数
/*
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();