PostgreSQL-存储过程使用入门

PostgreSQL-存储过程

基本语法

create function "public"."proc_h_testdemo"()
	returns void as $BODY$BEGIN
	-- 实体代码
	return;
end$BODY$   -- 方言 
	language plpgsql
1
2
3
4
5
6

returns:指定返回类型可以使复合类型

return：指定返回变量

create or replace  mycount() --创建 或者 替换
returns integer as $conuts$

declare  --声明
begin
	select count("id") into counts  --结果到 counts里
	from "table"
	return counts;
end ;
1
2
3
4
5
6
7
8
9

赋值

a:=b 是 a = b ，b的值符给a

|| 是字符串拼接符号

mysql:= 'select count('
	|| quote_ident(columnName)  --引用识别
	|| ')from'
	|| quote_ident(tableName);
1
2
3
4

游标

open ref_cursor for  --吧 下面的查询结果 赋值 ref_cursor
select name from table ;
1
2

v_sql:= 'select count(*) from table' || v_con;
open resultest for EXECUTE v_sql; 吧sql 执行完赋值给 v_sql
1
2

select count(*)  into v_count -- into 赋值 v_count
from freetopolice where name = P_name;
if v_count > 0 thne
1
2
3

GET DIAGNOSTIC 获取前面执行sql语句信息

影响行数

get diagnostics v_rowcount:=ROW_COUNT;
1

RAISE EXCEPTION ‘%’,v_result; 异常处理

例子用于 id 函数里面 出现异常 exception

IF v_rowcount != 1 THEN
	v_result:= 2000;
	RAISE EXCEPTION '%',v_result;
end if;
EXCEPTION
1
2
3
4
5

create or replace function "public"."postgid_raster"()
	returns "pg_catalog"."text" as 
	$BODY$ select '2.4.4'::text
1
2
3

clock_timestamp() 存储过程获提交时间

new() 是获取 提交时间

clock_timestamp 获取今天提交时间

return 返回类型

returns "pg_catalog"."int4"
returns "pg_catalog"."bool" 返回布尔类型
returns "pg_catalog"."refcursor" 返回名是 refcursor 游标
returns "pg_catalog"."text"
1
2
3
4

时间戳转字符

to_char(completeat,"YYYY-MM-DD") 时间戳转成字符串
to_timestamp相反
1
2

流程控制

条件

if ... then ... end if
if ... then ... else ... end if 
if ... then ... elsif ... then ... else ... end if

case ... when ... then ... else ... end case
case when ... then ... else ... end case
1
2
3
4
5
6

if -then

if 条件 then 主体 end if ;

if id = 1 then
	update table set name = 'lf' where id = 1; //id=1 时执行
end if ; 
1
2
3
4
5

if-then-else

if 条件 then 主体
else 主体
end if;

if name1 is not null then
	select account into r_account from table where name = name1; //account 账户
else
	raise ntice '用户名为空';
end if;
1
2
3
4
5
6
7
8
9

if - then - elseif

if 条件 then 主体
elseif 条件 then 主体
else 主体
end if;
1
2
3
4

case

case 搜索参数
when 值 then 主体
else 主体
end case;

case 
when 条件表达式 then 主体
else 主体
end case;
1
2
3
4
5
6
7
8
9

CASE account
	WHEN 'zhangsan','lisi' THEN  --值是包含其一
		msg := '账号为zhangsan或lisi'
	ELSE
		msg := '未找到账号'
END CASE;
或
CASE 
	WHEN account in ('zhangsan','lisi') THEN
		msg := '账号为zhangsan或lisi'
	ELSE
		msg := '未找到账号'
END CASE;
1
2
3
4
5
6
7
8
9
10
11
12
13

变量

1.psql命令使用变量
表数据如下：
hank=> select * from tb2;
 c1 |  c2   |             c3             
----+-------+----------------------------
  1 | hank  | 2018-02-06 10:08:00.787503
  2 | dazui | 2018-02-06 10:08:08.542481
  3 | wahah | 2018-02-06 10:08:15.468527
  4 | aaaaa | 2018-02-06 10:18:39.289523
 
SQL文本如下
cat  hank.sql 
select * from tb2 where c2=:name and c3>=:time;
通过psql查看
psql -v name="'hank'" -v time="'2018-02-06 10:08:00'" -f hank.sql
 c1 |  c2  |             c3             
----+------+----------------------------
  1 | hank | 2018-02-06 10:08:00.787503
或者
 psql -v name="'hank'" -v time="'2018-02-06 10:08:00'" -c '\i hank.sql'
 c1 |  c2  |             c3             
----+------+----------------------------
  1 | hank | 2018-02-06 10:08:00.787503
 
 效果一样
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

2.\set使用变量
hank=> \set name hank
hank=> \set time '2018-02-06 10:09:00'   
hank=>  select * from tb2 where c2=:'name' and c3>=:'time';
 c1 |  c2  |             c3             
----+------+----------------------------
  1 | hank | 2018-02-06 10:08:00.787503
1
2
3
4
5
6
7

3.通过定义参数实现
设置一个session级别的参数，通过current_setting取值
hank=>  set session "asasd.time" to "2018-02-06 10:09:00"; 
SET
hank=>  select * from tb2 where c3 >= current_setting('asasd.time')::timestamp;
 c1 |  c2   |             c3             
----+-------+----------------------------
  4 | aaaaa | 2018-02-06 10:18:39.289523
(1 row)
1
2
3
4
5
6
7
8
9

函数

流程控制

基本语法

变量

底部