目录

PostgreSQL - DML操作汇总

匿名函数

DO关键字

pl/pgsql即Procedural Language/ Postgres SQL(过程化sql语言),是Postgresql数据库对sql语句的扩展,可以在pl/pgsql代码块内定义多条sql语句,每条语句以分号结束,代码块由begin开始,end结束,代码块的最后一个end可以不加分号。

DO关键字用来执行一段匿名代码块,即在在程序语言过程中一次性执行的匿名函数。代码块可以看做是一段没有参数、没有返回值的函数体。其格式如下:

1
DO [LANGUAGE lang_name] code;

code

code block代码块实际上为一个字符串,可以用"美元符引用”$$书写字符串常量,$$中间可以包含标签名,可以自由命名,但是不能以数字开头,可以命名为$$,$_$,$a$,$a1$...,该标签名必须成对出现,且大小写敏感。

用DECLARE声明变量(如果不需要声明变量可以不写declare),用BEGIN和END包括需要执行的代码/sql语句,每个语句末尾需要加上分号,BEGIN不加分号,代码块最后一个END后可以省略分号;其格式如下:

1
2
3
4
5
DO $body$
BEGIN
  update student set name = '张小明' where id = 10010;
END
$body$;
1
2
3
4
5
6
7
8
DO $body$
DECLARE
  NEW_NAME varchar(100);
BEGIN
  NEW_NAME:='张'||'小明';
  update student set name = NEW_NAME where id = 10010;
END
$body$;

lang_name

用来解析code的程序语言的名字,如果缺省,默认为plpgsql,lang_name可以写在code前,也可以写在code后,即

1
DO code;

等效于下边的

1
DO LANGUAGE PLPGSQL code;

或者

1
DO code LANGUAGE PLPGSQL;

这里的code指的是代码块,也就是上边说的内容格式。

一个例子

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
DO $body$
DECLARE
  SIZES_VALUE varchar(1000);
  MEASUREMENT_TEMPLATE RECORD;

BEGIN
  FOR MEASUREMENT_TEMPLATE IN (SELECT ID FROM CNT_MEASUREMENT_TEMPLATE WHERE DOMAIN_ID IN ('/','RD1','RD2') AND IS_LATEST = TRUE AND (SIZES IS NULL OR SIZES = ''))
    LOOP
      -------- split sizes value from CNT_MEASUREMENT_TEMPLATE_SIZE order by seq_no --------
      SIZES_VALUE = (SELECT ARRAY_TO_STRING(ARRAY(SELECT ALT_LABEL||CASE WHEN COALESCE(LABEL,'') = '' THEN '' ELSE '('||LABEL||')' END FROM CNT_MEASUREMENT_TEMPLATE_SIZE 
      	WHERE PARENT_ID = MEASUREMENT_TEMPLATE.ID ORDER BY SEQ_NO),', '));

      -------- set sizes value for cnt_measurement_template whose sizes value is null or '' --------
	  UPDATE CNT_MEASUREMENT_TEMPLATE SET SIZES = SIZES_VALUE WHERE ID = MEASUREMENT_TEMPLATE.ID;
	  
    END LOOP;
END;
$body$ LANGUAGE PLPGSQL;

遍历数据,变量赋值

遍历操作以及变量赋值操作需要在匿名函数脚本中使用,且匿名函数在执行时必须显示开启事务。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
begin;

DO $body$
declare
targetId bpchar(32);
update_sr RECORD;
begin

    --遍历表中符合条件的数据
	for update_sr in (
        select temp.* from tab_test temp
        inner join tab_student sr on sr.id = temp.id
        where sr.is_latest is true
	)
	loop

        select max(id) into targetId from tab_student where ref_no = update_sr.ref_no;

        if targetId is not null then
        
            --操作数据等
        
        end if;
        
    end loop;
END;
$body$ LANGUAGE PLPGSQL;

commit;

上面的脚本中使用了for loop来遍历数据,需要注意的是,在遍历时会将数据缓存起来,如果在遍历时改变了被遍历的数据,是不会影响到被缓存的数据的。如果此时需要使用到被更改的值,只能通过赋值给临时变量来获取到被更改的值。

在变量赋值时,可以用select max(xx) into的写法。使用max()的目的是,如果select不到数据则会返回null,避免在变量赋值时出错。

插入其他表的数据

1
2
3
4
INSERT INTO tab_test
(id, name, ref_no, version)
select id, name, ref_no, version
from tab_student;

使用insert into ... select from ...来插入其他表的数据,也可以是同一个表,但此时需要起表别名来区分数据。且这种写法,需要两个表都存在才行。

修改多个列的值

在修改的列数量较少时,可以用下面的写法:

1
update tab_test set name = 'new name', ref_no = 'new ref_no' where id = '1';

如果列非常多时,比如同时改几十上百个,可以用下面的写法简单点:

1
2
3
update tab_test set (name, ref_no, version)
= ('new name', 'new ref_no', 2)
where id = '1';

update语句怎么关联其他表

PostgreSQL的update语句关联外表的写法与MySQL不同,不能直接通过join/outer join来关联其他表,就算使用update+join不会报错,join的那部分其实也不会有任何效果,如下所示:

1
2
3
4
5
6
7
8
update a
set value = 'test'
from a
join b on a.b_id = b.id
join c on b.c_id = c.id
where
a.key = 'test'
and c.value = 'test';

上边的sql本意是a、b、c三表关联,当c的value是’test’且a的key也是’test’的时候,就将a的value也改为’test’。

但实际上这个sql有大问题,这里的join和where条件并没有意义,一旦update成功,你会发现,a表内的所有数据的value都被改成了test!!要么update 0条数据,要么全部update!至于是哪种结果,这要看where的条件,目前还不清楚为什么会这样。因为这种写法本身就是不对的

PostgreSQL中正确的多表关联update写法

在update语句中不应该通过join来进行多表关联,而是要通过from来多表关联,如下:

1
2
3
4
5
6
7
8
update a
set value = 'test'
from b,c
where
a.b_id = b.id
and b.c_id = c.id
and a.key = 'test'
and c.value = 'test';

通过from来多表关联,而关联条件则是放到了where中,这样就可以达到我们想要的效果了。另外补充一句,对于set xxx = 'xxx'这个update的部分,是不可以在column字段前加上表前缀的,比如下边的写法就是有语法错误的:

1
2
update a
set a.value = 'test';

此外,update语句也可以连接自身的表,只要起了表别名将二者区分开来就行。

参考链接