PostgreSQL学习手册(SQL语言函数),自定义数据库函数

一、基本概念:

SQL函数可以包含任意数量的查询,但是函数只返回最后一个查询(必须是SELECT)的结果。在简单情况下,返回最后一条查询结果的第一行。如果最后一个查询不返回任何行,那么该函数将返回NULL值。如果需要该函数返回最后一条SELECT语句的所有行,可以将函数的返回值定义为集合,即SETOF sometype。
SQL函数的函数体应该是用分号分隔的SQL语句列表,其中最后一条语句之后的分号是可选的。除非函数声明为返回void,否则最后一条语句必须是SELECT。事实上,在SQL函数中,不仅可以包含SELECT查询语句,也可以包含INSERTUPDATEDELETE等其他标准的SQL语句,但是和事物相关的语句不能包含其中,如BEGINCOMMITROLLBACKSAVEPOINT等。
CREATE FUNCTION命令的语法要求函数体写成一个字符串文本。通常来说,该文本字符串常量使用美元符($$)围住,如:

  CREATE FUNCTION clean_emp() RETURNS void AS $$
  DELETE FROM emp WHERE salary < 0;
  $$ LANGUAGE SQL;

最后需要说明的是SQL函数中的参数,PostgreSQL定义$1表示第一个参数,$2为第二个参数并以此类推。如果参数是复合类型,则可以使用点表示法,即$1.name访问复合类型参数中的name字段。需要注意的是函数参数只能用作数据值,而不能用于标识符,如:

  INSERT INTO mytable VALUES ($1);   --合法
  INSERT INTO $1 VALUES (42);  --不合法(表名属于标示符之一)
  

二、基本类型:

最简单的SQL函数可能就是没有参数且返回基本类型的函数了,如:

  CREATE FUNCTION one() RETURNS integer AS $$
  SELECT 1 AS result;
  $$ LANGUAGE SQL;

下面的例子声明了基本类型作为函数的参数。

  CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
  SELECT $1 + $2;
  $$ LANGUAGE SQL;
  # 通过select调用函数。
  postgres=# SELECT add_em(1,2) AS answer;
   answer
  --------
  3
  (1 row)   

在下面的例子中,函数体内包含多个SQL语句,它们之间是用分号进行分隔的。

  CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
  UPDATE bank SET balance = balance - $2 WHERE accountno = $1;
  SELECT balance FROM bank WHERE accountno = $1;
  $$ LANGUAGE SQL;

三、复合类型:

见如下示例:
1).创建数据表,这样与之对应的复合类型也随之生成。

  CREATE TABLE emp (
  name  text,
  salary  numeric,
  age   integer,
  );

2).创建函数,其参数为复合类型。在函数体内,可以像引用基本类型参数那样引用复合类型,如$1。访问复合类型的字段使用点表达式即可,如:$1.salary

  CREATE FUNCTION double_salary(emp) RETURNS integer AS $$
  SELECT ($1.salary * 2)::integer AS salary;
  $$ LANGUAGE SQL;

3).在select语句中,可以使用emp.*表示emp表的一整行数据。

  SELECT name, double_salary(emp.*) AS dream FROM emp WHERE age > 30;

4).我们也可以使用ROW表达式构造自定义的复合类型,如:

  SELECT name, double_salary(ROW(name, salary*1.1, age)) AS dream FROM emp;

5).创建一个函数,其返回值为复合类型,如:

  CREATE FUNCTION new_emp() RETURNS emp AS $$
  SELECT ROW('None', 1000.0, 25)::emp;
  $$ LANGUAGE SQL;
  

6).调用返回复合类型的函数。

  SELECT new_emp();
  

7).调用返回复合类型的函数,同时访问该返回值的某个字段。

  SELECT (new_emp()).name;

四、带输出参数的函数:

还有一种方法可以用于返回函数执行的结果,即输出参数,如:

  CREATE FUNCTION add_em2 (IN x int, IN y int, OUT sum int) AS $$
  SELECT $1 + $2
  $$ LANGUAGE SQL;

调用方法和返回结果与add_em(带有返回值的函数)完全一致,如:

  SELECT add_em(3,7);

这个带有输出参数的函数和之前的add_em函数没有本质的区别。事实上,输出参数的真正价值在于它为函数提供了返回多个字段的途径。如,

  CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) AS $$
  SELECT $1 + $2, $1 * $2
  $$ LANGUAGE SQL;

调用方式没有改变,只是返回结果多出一列。

  SELECT * FROM sum_n_product(11,42);
   sum | product
  -----+---------
  53 |   462
  (1 row)

在上面的示例中,IN用于表示该函数参数为输入参数(缺省值,可以忽略),OUT则表示该参数为输出参数。

五、返回结果作为表数据源:

所有SQL函数都可以在查询的FROM子句里使用。该方法对于返回复合类型的函数而言特别有用,如果该函数定义为返回一个基本类型,那么该函数生成一个单字段表,如果该函数定义为返回一个复合类型,那么该函数生成一个复合类型里每个属性组成的行。见如下示例:
1).创建一个数据表。

  CREATE TABLE foo (
  fooid  int,
  foosubid int,
  fooname  text
  );

2).创建SQL函数,其返回值为与foo表对应的复合类型。

  CREATE FUNCTION getfoo(int) RETURNS foo AS $$
  SELECT * FROM foo WHERE fooid = $1;
  $$ LANGUAGE SQL;

3).在FROM子句中调用该函数。

  SELECT *, upper(fooname) FROM getfoo(1) AS t1;

六、返回集合的SQL函数:

如果SQL函数的返回值为SETOF sometype,那么在调用该函数时,将返回最后一个SELECT查询的全部数据。这个特性通常用于把函数放在FROM子句里调用,见如下示例:

  
  CREATE FUNCTION getfoo(int) RETURNS setof foo AS $$
  SELECT * FROM foo WHERE fooid = $1;
  $$ LANGUAGE SQL;  

在FROM子句中调用了返回复合类型集合的函数,其结果等同于:

  SELECT * FROM (SELECT * FROM foo WHERE fooid = 1) t1;
  
  SELECT * FROM getfoo(1) AS t1;  
  

七、多态的SQL函数:

SQL函数可以声明为接受多态类型(anyelement和anyarray)的参数或返回多态类型的返回值,见如下示例:
1).函数参数和返回值均为多态类型。

  
  CREATE FUNCTION make_array(anyelement, anyelement) RETURNS anyarray AS $$
  SELECT ARRAY[$1, $2];
  $$ LANGUAGE SQL;

其调用方式和调用其它类型的SQL函数完全相同,只是在传递字符串类型的参数时,需要显式转换到目标类型,否则将会被视为unknown类型,如:

  SELECT make_array(1, 2) AS intarray, make_array('a'::text, 'b') AS textarray;
  

2).函数的参数为多态类型,而返回值则为基本类型。

  CREATE FUNCTION is_greater(anyelement, anyelement) RETURNS boolean AS $$
  SELECT $1 > $2;
  $$ LANGUAGE SQL;

3).多态类型用于函数的输出参数。

  CREATE FUNCTION dup (f1 anyelement, OUT f2 anyelement, OUT f3 anyarray) AS $$
  SELECT $1, ARRAY[$1,$1]
  $$ LANGUAGE sql;

八、函数重载:

多个函数可以定义成相同的函数名,但是它们的参数一定要有所区分。换句话说,函数名可以重载,此规则有些类似于面向对象语言中的函数重载,见如下示例:

  CREATE FUNCTION test(int, real) RETURNS ...
  CREATE FUNCTION test(smallint, double) RETURNS ...

由于在PostgreSQL中函数支持重载,因此在删除函数时,也必须指定参数列表,如:

  DROP FUNCTION test(int, real);
  DROP FUNCTION test(smallint,double); 
已标记关键词 清除标记
相关推荐
<p> <span style="font-size:14px;">PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更名为PostgreSQL. PostgreSQL支持大部分SQL标准并且提供了许多其它现代特性:复杂查询、外键、触发器、视图、事务完整性等。</span> </p> <p> <span style="font-size:14px;"> </span> </p> <p> <a href="https://baike.baidu.com/item/PostgreSQL/530240"><span style="font-size:14px;">PostgreSQL</span></a><span style="font-size:14px;"> 是一个免费的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird),和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。</span> </p> <p> <span style="font-size:14px;"> </span> </p> <p> <span style="font-size:14px;">事实上, PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99,首先,它包括了可以说是目前世界上最丰富的数据类型的支持,其中有些数据类型可以说连商业数据库都不具备, 比如 IP 类型和几何类型等;其次,PostgreSQL 是全功能的自由软件数据库,很长时间以来,PostgreSQL 是唯一支持</span><a href="https://baike.baidu.com/item/%E4%BA%8B%E5%8A%A1"><span style="font-size:14px;">事务</span></a><span style="font-size:14px;">、</span><a href="https://baike.baidu.com/item/%E5%AD%90%E6%9F%A5%E8%AF%A2"><span style="font-size:14px;">子查询</span></a><span style="font-size:14px;">、多版本并行控制系统(</span><a href="https://baike.baidu.com/item/MVCC"><span style="font-size:14px;">MVCC</span></a><span style="font-size:14px;">)、</span><a href="https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%AE%8C%E6%95%B4%E6%80%A7"><span style="font-size:14px;">数据完整性</span></a><span style="font-size:14px;">检查等特性的唯一的一种自由软件的</span><a href="https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%AE%A1%E7%90%86%E7%B3%BB%E7%BB%9F"><span style="font-size:14px;">数据库管理系统</span></a><span style="font-size:14px;">。 Inprise 的 InterBase 以及</span><a href="https://baike.baidu.com/item/SAP"><span style="font-size:14px;">SAP</span></a><span style="font-size:14px;">等厂商将其原先专有软件开放为自由软件之后才打破了这个唯一。最后,PostgreSQL拥有一支非常活跃的开发队伍,而且在许多黑客的努力下,PostgreSQL 的质量日益提高。</span> </p> <p> <span style="font-size:14px;">从技术角度来讲,PostgreSQL 采用的是比较经典的</span><a href="https://baike.baidu.com/item/C%2FS"><span style="font-size:14px;">C/S</span></a><span style="font-size:14px;">(client/server)结构,也就是一个客户端对应一个服务器端</span><a href="https://baike.baidu.com/item/%E5%AE%88%E6%8A%A4%E8%BF%9B%E7%A8%8B"><span style="font-size:14px;">守护进程</span></a><span style="font-size:14px;">的模式,这个守护进程分析客户端来的查询请求,生成规划树,进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写,由</span><a href="https://baike.baidu.com/item/%E6%95%B0%E6%8D%AE%E5%BA%93%E6%9C%8D%E5%8A%A1%E5%99%A8"><span style="font-size:14px;">数据库服务器</span></a><span style="font-size:14px;">提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口,比如</span><a href="https://baike.baidu.com/item/ODBC"><span style="font-size:14px;">ODBC</span></a><span style="font-size:14px;">,</span><a href="https://baike.baidu.com/item/JDBC"><span style="font-size:14px;">JDBC</span></a><span style="font-size:14px;">,</span><a href="https://baike.baidu.com/item/Python"><span style="font-size:14px;">Python</span></a><span style="font-size:14px;">,</span><a href="https://baike.baidu.com/item/Perl"><span style="font-size:14px;">Perl</span></a><span style="font-size:14px;">,</span><a href="https://baike.baidu.com/item/Tcl"><span style="font-size:14px;">Tcl</span></a><span style="font-size:14px;">,</span><a href="https://baike.baidu.com/item/C%2FC%2B%2B"><span style="font-size:14px;">C/C++</span></a><span style="font-size:14px;">,</span><a href="https://baike.baidu.com/item/ESQL"><span style="font-size:14px;">ESQL</span></a><span style="font-size:14px;">等, 同时也要指出的是,PostgreSQL 对接口的支持也是非常丰富的,几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。</span> </p> <p> <span style="font-size:14px;"> </span> </p> <p> <strong><span style="font-size:14px;">本课程作为PostgreSQL数据库管理之三,主要讲解以下内容:</span></strong> </p> <p> <strong><br /></strong> </p> <p> <span style="font-size:14px;">1.</span><span style="font-size:14px;">     </span><span style="font-size:14px;">PostgreSQL约束讲解和剖析</span> </p> <p> <span style="font-size:14px;">2.</span><span style="font-size:14px;">     </span><span style="font-size:14px;">PostgreSQL数据类型</span> </p> <p> <span style="font-size:14px;">3.</span><span style="font-size:14px;">     </span><span style="font-size:14px;">PostgreSQL的结构管理</span> </p> <p> <span style="font-size:14px;">4.</span><span style="font-size:14px;">     </span><span style="font-size:14px;">PostgreSQL条件表达式和操作</span> </p> <p> <span style="font-size:14px;">5.</span><span style="font-size:14px;">     </span><span style="font-size:14px;">PostgreSQL使用小技巧</span> </p> <p> <br /></p> <p> <img src="https://img-bss.csdn.net/201906140548008383.png" alt="" /></p>
<p> PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES,现在已经更名为PostgreSQL. PostgreSQL支持大部分SQL标准并且提供了许多其它现代特性:复杂查询、外键、触发器、视图、事务完整性等。 </p> <p>   </p> <p> PostgreSQL 是一个免费的对象-关系数据库服务器(数据库管理系统),它在灵活的 BSD-风格许可证下发行。它提供了相对其他开放源代码数据库系统(比如 MySQL 和 Firebird),和专有系统(比如 Oracle、Sybase、IBM 的 DB2 和 Microsoft SQL Server)之外的另一种选择。 </p> <p>   </p> <p> 事实上, PostgreSQL 的特性覆盖了 SQL-2/SQL-92 和 SQL-3/SQL-99,首先,它包括了可以说是目前世界上最丰富的数据类型的支持,其中有些数据类型可以说连商业数据库都不具备, 比如 IP 类型和几何类型等;其次,PostgreSQL 是全功能的自由软件数据库,很长时间以来,PostgreSQL 是唯一支持事务、子查询、多版本并行控制系统(MVCC)、数据完整性检查等特性的唯一的一种自由软件的数据库管理系统。 Inprise 的 InterBase 以及SAP等厂商将其原先专有软件开放为自由软件之后才打破了这个唯一。最后,PostgreSQL拥有一支非常活跃的开发队伍,而且在许多黑客的努力下,PostgreSQL 的质量日益提高。 </p> <p> <br /></p> <p> 从技术角度来讲,PostgreSQL 采用的是比较经典的C/S(client/server)结构,也就是一个客户端对应一个服务器端守护进程的模式,这个守护进程分析客户端来的查询请求,生成规划树,进行数据检索并最终把结果格式化输出后返回给客户端。为了便于客户端的程序的编写,由数据库服务器提供了统一的客户端 C 接口。而不同的客户端接口都是源自这个 C 接口,比如ODBC,JDBC,Python,Perl,Tcl,C/C++,ESQL等, 同时也要指出的是,PostgreSQL 对接口的支持也是非常丰富的,几乎支持所有类型的数据库客户端接口。这一点也可以说是 PostgreSQL 一大优点。 </p> <p>   </p> <p> <strong>本课程作为PostgreSQL数据库管理一,主要讲解以下内容:</strong> </p> <p> <strong><br /></strong> </p> <p> 1.<span style="font-size:9px;">     </span>PostgreSQL 存储过程基本知识 </p> <p> 2.<span style="font-size:9px;">     </span>PostgreSQL 用户自定义函数 </p> <p> 3.<span style="font-size:9px;">     </span>PostgreSQL 控制结构 </p> <p> 4.<span style="font-size:9px;">     </span>PostgreSQL 游标和存储过程 </p> <p> 5.<span style="font-size:9px;">     </span>PostgreSQL 索引 </p> <p> 6.<span style="font-size:9px;">     </span>PostgreSQL 视图 </p> <p> 7.<span style="font-size:9px;">     </span>PostgreSQL 触发器 </p> <p> 8.<span style="font-size:9px;">     </span>PostgreSQL 角色、备份和还原 </p> <p> 9.<span style="font-size:9px;">     </span>PostgreSQL 表空间管理 </p> <p> <br /></p> <p> <img src="https://img-bss.csdn.net/201906211032373758.png" alt="" /></p>
©️2020 CSDN 皮肤主题: 书香水墨 设计师:CSDN官方博客 返回首页