postgresql笔记

PostgreSQL学习使用过程中的细小知识

select into

动态sql中不能使用select into，如下面这样会报错：

CREATE or Replace FUNCTION func_getnextid(  
    tablename varchar(240),  
    idname varchar(20) default 'id')  
RETURNS integer AS $funcbody$  
Declare  
    sqlstring varchar(240);  
    currentId integer;  
Begin  
    sqlstring:= 'select max("' || idname || '") into currentId from "' || tablename || '";';  
    EXECUTE sqlstring;  
    if currentId is NULL or currentId = 0 then  
        return 1;  
    else  
        return currentId + 1;  
    end if;  
End;  
$funcbody$ LANGUAGE plpgsq

执行后出现这样的错误：

SQL error:

ERROR:  EXECUTE of SELECT ... INTO is not implemented

CONTEXT:  PL/pgSQL function "func_getnextbigid" line 6 at EXECUTE statement

改成这样的就对了：

CREATE or Replace FUNCTION func_getnextid(  
    tablename varchar(240),  
    idname varchar(20) default 'id')  
RETURNS integer AS $funcbody$  
Declare  
    sqlstring varchar(240);  
    currentId integer;  
Begin  
    sqlstring:= 'select max("' || idname || '") from "' || tablename || '";';  
    EXECUTE sqlstring into currentId;  
    if currentId is NULL or currentId = 0 then  
        return 1;  
    else  
        return currentId + 1;  
    end if;  
End;  
$funcbody$ LANGUAGE plpgsql;

注意：EXECUTE后面的字符串必须是一个语句，不能是一个表达式。

批量插入

• 使用insert into ... select的方法

insert into tbl1 (id, info ,crt_time) select generate_series(1,10000),'test',now();

• 使用values(),(),...();的方法

postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now()), (2,'test2',now()), (3,'test3',now());

• BEGIN; …多条insert…; END;
  严格来说，这应该不属于批量，但是可以减少事务提交时的同步等待。同样有性能提升的效果。

postgres=# begin;    
BEGIN    
postgres=# insert into tbl1 (id,info,crt_time) values (1,'test',now());    
INSERT 0 1    
postgres=# insert into tbl1 (id,info,crt_time) values (2,'test2',now());    
INSERT 0 1    
postgres=# insert into tbl1 (id,info,crt_time) values (3,'test3',now());    
INSERT 0 1    
postgres=# end;    
COMMIT

批量更新

update test set info=tmp.info 
from (values (1,'new1'),(2,'new2'),(6,'new6')) as tmp (id,info) 
where test.id=tmp.id;

批量删除

delete from test 
using (values (3),(4),(5)) as tmp(id) 
where test.id=tmp.id;

赏

×

纯属好玩

扫码打赏，你说多少就多少

打开支付宝扫一扫，即可进行扫码打赏哦