PostgreSQL (零) 知识点积累

0.启动和关闭服务

sudo service postgresql start
sudo service postgresql stop
sudo service postgresql restart

1.case…when…else…end

语法

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

示例

case
	when (xxx = '1') then 1
	when (xxx = '2') then 2
	when (xxx = '3') then 3
	else 0
end as xxx

2.判断字符串是否为数字，是则转为数字

case
	when (xxx = '1') then 1
	when (xxx = '2') then 2
	when (xxx = '3') then 3
	when (xxx ~ '^([0-9]?[0-9]+)$') then to_number(xxx, '999')
	else 0
end as priority

其中，xxx ~ '^([0-9]?[0-9]+)$'的～为正则表达式，to_number(xxx, '999')方法中第一个参数为表中字段，第二个参数为数字的规则，999代表三位数字，但第二个参数必须为字符串。

3.coalesce和ilike的使用

select coalesce(user_name,'') from table_user where user_id ilike '%fracong%'

说明:

• 1.返回user_name的时候,如果user_name为null,则设置为’';
• 2.ilike的使用:
  • 2.1.不区分大小写,而like是区分大小写的.
  • 2.2.ilike可以和以下SQL进行替换,其中?为user_id

select coalesce(user_name,'') from table_user where (coalesce(?, '') = '' or upper(user_id) like (concat('%', upper(?), '%')))

4.substring的使用

截取语法：substring(filed from start_num for length)
start_num为开始的位置(从1开始计数，包含1；如果为负数，则还是从1开始，length的长度相应递减)，length为截取的长度。

如下例子：

select user_name from user_table when user_name = '22fracong111';

结果：22fracong111

select substring(user_name from 3 for 9) from user_table when user_name = '22fracong111';

结果：fracong

5.对列进行操作

5.1.删除列

alter table table_name drop column column_name;
alter table table_name drop column column_name cascade;//删除外健依赖

5.2.新增列

alter table table_name add column column_name varchar(10);
alter table table_name add column column_name integer;
alter table table_name add column column_name varchar(10) not null;
alter table table_name add column column_name varchar(10) default '';

6.存储过程

6.1.基本模板

CREATE OR REPLACE FUNCTION func_test(v_key integer, OUT v_message text)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	v_test text; -- 定义变量
begin
	v_message = 'success';// 返回值
	return;
end;

说明：OUT为返回值

6.2.if判断

CREATE OR REPLACE FUNCTION func_test(v_key integer, OUT v_message text)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	v_test text; -- 定义变量
begin
	v_message = 'success';// 返回值
	if v_key == 2 then
		v_message = 'query OK';
	end if;
	return;
end;

说明：OUT为返回值

6.3.异常处理

CREATE OR REPLACE FUNCTION func_test(v_key integer, OUT v_message text)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	v_test text; -- 定义变量
begin
	v_message = 'success';// 返回值
	begin
		if v_key == 2 then
			RAISE EXCEPTION 'Err: %','test is not 2';
		end if;
		exception
			when others then
				v_message = 'error';
				return;
	end;
	return;
end;

说明：异常处理需要放在begin和end之间，使用exception进行抓取不同的异常。

6.4.打印日志

raise notice 'info log:%','log info message';

6.5.for循环

CREATE OR REPLACE FUNCTION func_test(v_key integer, OUT v_message text)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	v_test text; -- 定义变量
begin
	v_message = 'success'; -- 返回值
	for v_test in select * from table_test    --for循环，for后面参数进行循环赋值，使用loop开始，使用end loop结束
	loop
		if v_test.key == v_key then
			v_message = 'query OK';
			exit;-- 退出循环，如果想直接结束方法，可以使用return
		end if;
	end loop;
	return;
end;

6.6.赋值查询结果

CREATE OR REPLACE FUNCTION func_test(v_key integer, OUT v_message text)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	v_test text; -- 定义变量
begin
	v_message = 'success'; -- 返回值
	select key into v_test from table_test; -- 使用into进行赋值
	return;
end;

6.7.判断是否为空

CREATE OR REPLACE FUNCTION func_test(v_key integer, OUT v_message text)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
declare
	v_test text; -- 定义变量
begin
	v_message = 'success'; -- 返回值
	if coalesce(v_key, 0) <> 0 then -- 判断v_key是否为空,其中v_key为数字
		v_message = 'OK1';
	end if;
	if coalesce(v_test, '') <> '' then -- 判断v_key是否为空,其中v_test为文本
		v_message = 'OK2';
	end if;
	return;
end;

7.异常

7.1.current transaction is aborted, commands ignored until end of transaction block

原因：在开启事务之后，如果在事务过程中，某个SQL执行发生异常，后面再执行其他的SQL的时候，就会出现这个错误