PostgreSQL-存储过程
文章目录
基本语法
create function "public"."proc_h_testdemo"()
returns void as $BODY$BEGIN
-- 实体代码
return;
end$BODY$ -- 方言
language plpgsql
returns:指定返回类型可以使复合类型
return:指定返回变量
create or replace mycount() --创建 或者 替换
returns integer as $conuts$
declare --声明
begin
select count("id") into counts --结果到 counts里
from "table"
return counts;
end ;
赋值
a:=b
是 a = b ,b的值符给a
||
是字符串拼接符号
mysql:= 'select count('
|| quote_ident(columnName) --引用识别
|| ')from'
|| quote_ident(tableName);
游标
open ref_cursor for --吧 下面的查询结果 赋值 ref_cursor
select name from table ;
v_sql:= 'select count(*) from table' || v_con;
open resultest for EXECUTE v_sql; 吧sql 执行完赋值给 v_sql
select count(*) into v_count -- into 赋值 v_count
from freetopolice where name = P_name;
if v_count > 0 thne
GET DIAGNOSTIC 获取前面执行sql语句信息
影响行数
get diagnostics v_rowcount:=ROW_COUNT;
RAISE EXCEPTION ‘%’,v_result; 异常处理
例子用于 id 函数里面 出现异常 exception
IF v_rowcount != 1 THEN v_result:= 2000; RAISE EXCEPTION '%',v_result; end if; EXCEPTION
create or replace function "public"."postgid_raster"()
returns "pg_catalog"."text" as
$BODY$ select '2.4.4'::text
clock_timestamp() 存储过程获提交时间
new() 是获取 提交时间
clock_timestamp 获取今天提交时间
return 返回类型
returns "pg_catalog"."int4"
returns "pg_catalog"."bool" 返回布尔类型
returns "pg_catalog"."refcursor" 返回名是 refcursor 游标
returns "pg_catalog"."text"
时间戳转字符
to_char(completeat,"YYYY-MM-DD") 时间戳转成字符串
to_timestamp相反
流程控制
条件
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
if -then
if 条件 then 主体 end if ;
if id = 1 then
update table set name = 'lf' where id = 1; //id=1 时执行
end if ;
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;
if - then - elseif
if 条件 then 主体
elseif 条件 then 主体
else 主体
end if;
case
case 搜索参数
when 值 then 主体
else 主体
end case;
case
when 条件表达式 then 主体
else 主体
end case;
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.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
效果一样
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
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)