分类导航

Postgre SQL 发布时间：2022-05-20 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了PostgreSQL9.3 存储过程与触发器入门，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

之前还真不知道POSTGResql可以构造函数，好吧，我是小白我怕谁呀。存储过程和触发器的核心都是函数。

使用PostgreSQL构造函数的方式大致相当可能会因为版本而不同，大同小异。

仅入门：

一、存储过程结构：

(关于最基本的结构，http://blog.chinaunix.net/uID-7591044-ID-1742967.HTML 这篇文章写的足够简单容易理解)

Create or replace function 过程名(参数名参数类型,…..) returns 返回值类型 as

$body$

//声明变量

Declare

变量名变量类型；

如：

flag Boolean;

变量赋值方式（变量名类型：=值；）

如：

str text :=值; / str text; str :=值；

Begin

函数体；

return 变量名； //存储过程中的返回语句

End;

$body$

Language plpgsql;

二、变量类型：

除了POSTGResql内置的变量类型外，常用的还有 RECORD ，表示一条记录。

整数数据类型：

@H_489_56@ 子类型	@H_489_56@ 标准名	@H_489_56@ 描述
@H_489_56@ smalll Integer	@H_489_56@ smallint	@H_489_56@ 一个2字节的符号型整数，可以存储-32768到32767的数字
@H_489_56@ Integer	@H_489_56@ Int	@H_489_56@ 一个4字节的符号型整数，可以存储-2147483648到2147473647的数字
@H_489_56@ serial	@H_489_56@ 和Integer一样，除了它的值通常是由POSTGResql自动输入的。

浮点数据类型：（浮点数据也可以再细分，分为提供通用功能的浮点值和固定精度的数字）

@H_489_56@ 子类型	@H_489_56@ 标准名	@H_489_56@ 描述
@H_489_56@ float	@H_489_56@ float(n)	@H_489_56@ 支持最少精度为n，存储为最多8字节的浮点数。
@H_489_56@ float8	@H_489_56@ real	@H_489_56@ 双精度（8字节）浮点数字
@H_489_56@ numeric	@H_489_56@ numeric(p,s)	@H_489_56@ 拥有p个数字的实数，其中小数点后有s位。不像float，这始终是一个确切的数字，但工作效率比普通浮点数字低。
@H_489_56@ money	@H_489_56@ numeric(9,2)	@H_489_56@ POSTGResql特有的类型，但在其他数据库里也普遍存在。Money类型从POSTGResql 8.0开始不赞成使用，且可能在以后版本中取消。你应该使用number类型代替。

注：

存储float和real类型的数据的行为非常相似，但是numeric列的行为有点不同。Numeric类型不是存储接近的数，而是在小数后面进行后超出固定长度的部分进行四舍五入。如果我们存储太大的数据到其中，INSERT将失败。还要注意float和real也会对数字四舍五入；例如123.456789被四舍五入为123.457。

时间数据类型：

定义	意义
date	存储日期信息
time	存储时间信息
timestamp	存储日期和时间
interval	存储timestamp之间差别的信息
timestamptz	POSTGResql扩展的类型，存储包含时区信息的timestamp

特殊数据类型：

@H_489_56@ 定义	@H_489_56@ 意义
@H_489_56@ Box	@H_489_56@ 矩形盒子
@H_489_56@ line	@H_489_56@ 一组点
@H_489_56@ point	@H_489_56@ 一对几何学的数字
@H_489_56@ lseg	@H_489_56@ 一条线段
@H_489_56@ polygon	@H_489_56@ 一条封闭的几何线
@H_489_56@ cIDr或inet	@H_489_56@ 一个IPv4的地址，录入192.168.0.1
@H_489_56@ macaddr	@H_489_56@ 以MAC地址（以太网卡物理地址）

注：POSTGResql也允许你使用sql命令CREATE TYPE在数据库中建立你自己的类型。这通常不需要，而且在一定程度上，它是POSTGResql独有的。

数组

通常，一个数组需要通过使用一个附加表实现。但是，数组的能力有时候很有用。建立数组的方法有两种：传统的POSTGResql的方法和sql99标准的方法。

POSTGResql样式的数组

要将一个表的列定义为数组，你可以简单地在类型后面添加[]；不需要定义元素的个数。即使定义了个数，也不会强制要求存储的个数。@H_403_362@

Eg：

test=> create table empworkday (

test(> refcode char(5),

test(> workdays int[]

test(> );

往数组列中插入值：

test=> INSERT INTO empworkday VALUES(‘val01′,‘{0,1,1}’);

test=> INSERT INTO empworkday VALUES(‘val02′,1}’);

sql99样式的数字

在sql99标准中，必须指出元素的个数。

Eg:

test=> create table empworkday (

test(> refcode char(5),

test(> workdays int arraY[7]

test(> );

test=> INSERT INTO empworkday VALUES(‘val01′,1}’);

三、连接字符：

POSTGResql存储过程中的连接字符不再是“+”,而是使用“||”。

四、控制结构：

1、if 条件（五种形式）

IF ... THEN

IF ... THEN ... ELSE

IF ... THEN ... ELSE IF

IF ... THEN ... ELSIF ... THEN ... ELSE

IF ... THEN ... ELSEIF ... THEN ... ELSE（注：ELSEIF 是ELSIF 的别名）

2、循环

使用LOOP，EXIT，CONTinUE，WHILE，和FOR 语句，可以控制PL/pgSQL 函数重复一系列命令。

1）、LOOP

[ <<label>> ]

LOOP

 statements

END LOOP [ label ];

LOOP 定义一个无条件的循环，无限循环，直到由EXIT或者RETURN语句终止。可选的label 可以由EXIT 和CONTinUE 语句使用，用于在嵌套循环中声明应该应用于哪一层循环。

2）、EXIT

	EXIT [ label ] [ WHEN Expression ];

	如果没有给出label， 那么退出最内层的循环，然后执行跟在 END LOOP 后面的语句。 如果给出 label， 那么它必须是当前或者更高层的嵌套循环块或者语句块的标签。然后该命名块或者循环就会终止，而控制落到对应循环/块的 END 语句后面的语句上。

如果声明了WHEN，循环退出只有在Expression 为真的时候才发生，否则控制会落到EXIT 后面的语句上。

EXIT 可以用于在所有的循环类型中，它并不仅仅限制于在无条件循环中使用。在和 BEGIN 块一起使用的时候，EXIT 把控制交给块结束后的下一个语句。

例如:

Loop 循环

If … then 条件判断

Exit ; 条件成立，则退出循环。

End if;

End loop;

3）、CONTinUE

CONTinUE [label ] [ WHENExpression ];@H_403_362@

如果没有给出 label，那么就开始最内层的循环的下一次执行。也就是说，控制传递回给循环控制表达式（如果有），然后重新计算循环体。如果出现了label，它声明即将继续执行的循环的标签。

如果声明了 WHEN，那么循环的下一次执行只有在Expression 为真的情况下才进行。否则，控制传递给CONTinUE 后面的语句。

CONTinUE 可以用于所有类型的循环；它并不仅仅限于无条件循环。

例如:

 一些计算

 EXIT WHEN count > 100;

 CONTinUE WHEN count < 50;

 一些在count 数值在 [50 .. 100] 里面时候的计算

END LOOP;@H_403_362@

4）、WHILE

	[ <<label>> ]

	WHILE Expression LOOP

 	statements

	END LOOP [ label ];

只要条件表达式为真，WHILE语句就会不停在一系列语句上进行循环. 条件是在每次进入循环体的时候检查的.

例如:

WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP

 -- 可以在这里做些计算

END LOOP;

WHILE NOT BooleAN_Expression LOOP

5）、FOR（整数变种）

	FOR name IN [ REVERSE ] Expression .. Expression LOOP

	END LOOP [ labal ];

这种形式的FOR对一定范围的整数数值进行迭代的循环。变量name 会自动定义为Integer类型并且只在循环里存在。给出范围上下界的两个表达式在进入循环的时候计算一次。迭代步进值总是为 1，但如果声明了REVERSE就是 -1。

一些整数FOR循环的例子∶

FOR i IN 1..10 LOOP 表示1循环到10

 这里可以放一些表达式

 RAISE NOTICE 'i IS %',i;

FOR i IN REVERSE 10..1 LOOP@H_403_362@

END LOOP;

如果下界大于上界（或者是在 REVERSE 情况下是小于），那么循环体将完全不被执行。而且不会抛出任何错误。

3、异常捕获

EXCEPTION

WHEN 错误码（如：StriNG_DATA_RIGHT_TruncATION：字串数据右边被截断） THEN

/**后台打印错误信息*/

RAISE NOTICE '错吴信息';

五、示例代码：

/**

批量插入一批数据，经纬度字段值要满足中国地理位置上的经纬度范围；

注：时间不能指定为同一时间，否则会扫描全表，导致性能低下。下列脚本未考虑时间的分段，采用的一个时间点。

create orreplace function intobatch() returns Integer as

$body$

declare

skyid integer;

lot float;

lat float;

sex varchar;

level Integer;

ctim E int :=1325404914;

num Integer :=0;

@R_814_10586@l Integer :=0;

begin

lot='73.6666666';

lat='3.8666666';

FOR skyID IN 404499817 ..404953416 loop

if(lot > 135.0416666) then

lot=73.6666666;

end if;

if(lat > 53.5500000) then

lat=3.8666666;

end if;

if(skyID%2 <> 0) then

sex='1';

level=0;

else

sex='2';

level=1;

end if;

INSERT INTO user_last_LOCATIOn(user_iD,app_ID,lonlat,sex,accurate_level,lonlat_point,create_timE)

VALUES(skyID,2934,ST_GeomFromText('POINT('||lot||' '||lat||')',4326),level,POINT(lot,lat),to_timestamp(ctim E));

lot=lot+0.1;

lat=lat+0.1;

skyID=skyID+1;

end loop;

return skyID;

end

$body$

languageplpgsql;

SELECT *from intobatch();

【这篇文章介绍的也可以】blog.csdn.net/voipmaker/article/details/6253115

然后就是触发器：

POSTGResql的触发器是数据库自动执行\指定的数据库事件发生时调用的回调函数。以下是有关POSTGResql的触发器的要点：www.yiibai.com@H_403_362@

POSTGResql的触发可以指定触发操作前尝试一行（在检查约束之前INSERT，updatE或DELETE）或操作完成后（在检查约束之后和INSERT，updatE或deletE（删除）已完成），或替代的操作（在视图上插入，更新或删除的情况下）..
FOR EACH ROW触发器被标记的操作修改的每一行被称为一次。相比之下，FOR EACH STATEMENT触发器为只执行一次对于任何给定的操作，不管它有多少行修改。
WHEN子句和触发器动作可能访问的行元素被插入，删除或更新使用的形式NEW.column-name和olD.column-name，其中列名是从表中的列名的引用该触发器相关联的。www.yiibai.com@H_403_362@
如果提供WHEN子句，POSTGResql的报表只执行WHEN子句为true的行。如果没有提供WHEN子句，POSTGResql的语句执行的所有行。
如果有多个相同类型的触发器定义了相同的事件，他们将被触发名称是按字母顺序排列。
BEFORE，AFTER或INSTEAD OF关键字决定何时触发动作将被执行，相对于插入，修改或移除相关的行。www.yiibai.com@H_403_362@
触发器表，它们与丢弃时自动删除。yiibai.com@H_403_362@
要修改的表必须存在，在同一数据库中的表或视图，触发器被附加，必须使用表名而不使用database.tablename。
约束时指定的选项创建一个约束触发器。这是一个普通的触发器除外，可以调整使用SET（设定）约束的触发器触发的定时相同。预计约束触发器违反他们所实施的限制时引发异常..

语法:

创建触发器的基本语法如下：

CREATE  trigGER trigger_name @H_403_362@[@H_403_362@BEFORE@H_403_362@@H_280_696@|@H_403_362@AFTER@H_403_362@@H_280_696@|@H_403_362@INSTEAD OF@H_403_362@@H_280_696@]@H_403_362@ event_name
ON table_name
@H_403_362@@H_280_696@[@H_403_362@
 @H_403_362@@H_280_696@--@H_403_362@ @H_403_362@trigger@H_403_362@ logic goes here@H_403_362@@H_280_696@....@H_403_362@
@H_403_362@@H_280_696@];@H_403_362@

Hereevent_name@H_403_362@Could beINSERT,deletE,updatE,@H_403_362@andTruncATE@H_403_362@database operation on the mentioned tabletable_name@H_403_362@. You can optionally specify FOR EACH ROW after table name.

Following is the Syntax of creaTing a trigger on an updatE operation on one or more specifIEd columns of a table as follows:

]@H_403_362@ updatE OF column_name
ON table_name
@H_403_362@@H_280_696@];@H_403_362@

例子

让我们考虑一个情况下，我们要保持审核COMPANY表中的每一条记录被插入，我们将创建新如下（如果已经创建过，那么删除COMPANY表）

testdb@H_403_362@@H_280_696@=#@H_403_362@ create table COMPANY@H_403_362@@H_280_696@(@H_403_362@
   ID INT PRIMary KEY     NOT NulL@H_403_362@@H_280_696@,@H_403_362@
   name           TEXT    NOT NulL@H_403_362@:rgb(102,@H_403_362@
   AGE            INT     NOT NulL@H_403_362@:rgb(102,@H_403_362@
   ADDRESS        CHAR@H_403_362@@H_280_696@(@H_403_362@50@H_403_362@@H_280_696@),@H_403_362@
   SALARY         REAL
@H_403_362@@H_280_696@);@H_403_362@

为了保持审核的测试，我们将创建一个新的表被称为审计将被插入日志消息每当有一个新的记录条目表COMPANY：www.yiibai.com@H_403_362@

=#@H_403_362@ create table AUDIT@H_403_362@@H_280_696@(@H_403_362@
    EMP_ID int nOT NulL@H_403_362@:rgb(102,@H_403_362@
    ENTRY_date text NOT NulL
@H_403_362@ 这里的ID是审计记录ID，EMP_ID的ID来自COMPANY表，日期将保持公司表时的记录将被创建时间戳。所以，现在让我们创建一个触发器，COMPANY表如下： 
 
  
 =#@H_403_362@ CREATE trigGER example_trigger AFTER INSERT ON COMPANY
FOR EACH ROW EXECUTE PROCEDURE auditlogfunc@H_403_362@@H_280_696@();@H_403_362@   
  auditlogfunc（）是一个POSTGResql的过程，有以下定义： 
   
 CREATE OR replaCE FUNCTION auditlogfunc@H_403_362@@H_280_696@()@H_403_362@ RETURNS trigGER AS $example_table$
    @H_403_362@BEGIN@H_403_362@
        INSERT INTO AUDIT@H_403_362@@H_280_696@(@H_403_362@EMP_ID@H_403_362@:rgb(102,@H_403_362@ ENTRY_DATE@H_403_362@@H_280_696@)@H_403_362@ VALUES @H_403_362@@H_280_696@(@H_403_362@new@H_403_362@@H_280_696@.@H_403_362@ID@H_403_362@:rgb(102,@H_403_362@ CURRENT_TIMESTAMP@H_403_362@@H_280_696@);@H_403_362@
        RETURN NEW@H_403_362@@H_280_696@;@H_403_362@
    @H_403_362@END@H_403_362@@H_280_696@;@H_403_362@
$example_table$ LANGUAGE plpgsql@H_403_362@@H_280_696@;@H_403_362@   
  现在，让我们开始COMPANY表插入记录，这将导致在审核表中创建审计日志记录。因此，让我们创建一个COMPANY表记录如下： 
   
 =#@H_403_362@ INSERT INTO COMPANY @H_403_362@@H_280_696@(@H_403_362@ID@H_403_362@:rgb(102,@H_403_362@name@H_403_362@:rgb(102,@H_403_362@AGE@H_403_362@:rgb(102,@H_403_362@ADDRESS@H_403_362@:rgb(102,@H_403_362@SALARY@H_403_362@@H_280_696@)@H_403_362@
VALUES @H_403_362@1@H_403_362@:rgb(102,@H_403_362@ @H_403_362@'Paul'@H_403_362@:rgb(102,@H_403_362@ @H_403_362@32@H_403_362@@H_280_696@'California'@H_403_362@20000.00@H_403_362@ @H_403_362@ 这将创建一个记录COMPANY表如下：yiibai.com@H_403_362@ 
 
 ID | name | age | address      | salary
----+------+-----+--------------+--------
  1 | Paul |  32 | California   |  20000   
  同时审核表中创建一条记录。这条记录是一个触发我们已经创建了COMPANY表上的INSERT操作的结果。类似的方式，也可以创建触发器，updatE和deletE操作根据要求。 
  emp_ID |          entry_date
--------+-------------------------------
      1 | 2013-05-05 15:49:59.968+05:30
(1 row)
   
  列出触发器 
  可以列出当前数据库中的所有触发器从pg_trigger表如下： 
   
 =#@H_403_362@ SELECT @H_403_362@@H_280_696@*@H_403_362@ FROM pg_trigger@H_403_362@@H_280_696@;@H_403_362@ yiibai.com@H_403_362@  
  以上POSTGResql的表会列出所有触发器。 
  如果要列出特定表上的触发器，然后使用条款与表名如下： 
   
 =#@H_403_362@ SELECT tgname FROM pg_trigger@H_403_362@:rgb(102,@H_403_362@ pg_class WHERE tgrelID@H_403_362@@H_280_696@=@H_403_362@pg_class@H_403_362@@H_280_696@.@H_403_362@oID AND relname@H_403_362@@H_280_696@=@H_403_362@'company'@H_403_362@ 以上POSTGResql的表也会列出只有一个条目如下： 
 
     tgname
-----------------
 example_trigger
(1 row)
   
  删除触发器 
  以下是DROP命令可以用来删除一个现有的触发：yiibai.com@H_403_362@
 
 
=#@H_403_362@ DROP trigGER trigger_name@H_403_362@@H_280_696@;@H_403_362@ yiibai.com@H_403_362@

触发器内容转自:http://www.yiibai.com/html/POSTGResql/2013/080673.html（这个网站不错）@H_403_362@

@H_403_362@

该博主的关于POSTGResql函数的介绍也很好@H_403_362@

另一个写的很好的触发器博客，和上一个的不同在与他详细介绍了数据可见规则@H_403_362@和事例，@H_403_362@

blog.csdn.net/neo_liu0000/article/details/6255623
@H_403_362@@H_403_362@

@H_403_362@@H_403_362@

这个链接也很好：www.cnblogs.com/stephen-liu74/category/343171.HTML@H_403_362@@H_403_362@

大佬总结

以上是大佬教程为你收集整理的PostgreSQL9.3 存储过程与触发器入门全部内容，希望文章能够帮你解决PostgreSQL9.3 存储过程与触发器入门所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：好吧

上一篇: postgresql的一些操作记录下一篇:用PostgreSQL计算两个日期的差（...

猜你在找的Postgre SQL相关文章

PostgreSQL 与 SQL Server 的数据类型映射 2022-05-20
postgresql – 在AWS Redshift中使用Group By计算中值 2019-11-06
了解postgreSQL共享内存 2019-11-06
在PostgreSQL表中生成测试数据 2019-11-06
与Postgres regexp_matches多次匹配 2019-11-06
postgresql – PgAdmin致命错误.无法联系应用服务器 2019-11-06
为什么这个PostgreSQL全文搜索会在不同的(RDS)服务器上中断？ 2019-11-06
postgresql – Postgres使用JSONB INSERT ON CONFLICT 2019-11-06
postgresql – Postgres INSERT ON CONFLICT DO UPDATE vs INSERT或UPDATE 2019-11-06
hydra 安装 2019-11-06