PostgreSQL学习手册(SQL语言函数)

一、基本概念：

    SQL函数可以包含任意数量的查询，但是函数只返回最后一个查询(必须是SELECT)的结果。在简单情况下，返回最后一条查询结果的第一行。如果最后一个查询不返回任何行，那么该函数将返回NULL值。如果需要该函数返回最后一条SELECT语句的所有行，可以将函数的返回值定义为集合，即SETOF sometype。
    SQL函数的函数体应该是用分号分隔的SQL语句列表，其中最后一条语句之后的分号是可选的。除非函数声明为返回void，否则最后一条语句必须是SELECT。事实上，在SQL函数中，不仅可以包含SELECT查询语句，也可以包含INSERT、UPDATE和DELETE等其他标准的SQL语句，但是和事物相关的语句不能包含其中，如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。
    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); 
时间： 2024-10-13 10:12:08

PostgreSQL学习手册(SQL语言函数)的相关文章

PostgreSQL学习手册(目录)

原文地址:http://www.cnblogs.com/stephen-liu74/archive/2012/06/08/2315679.html     事实上之前有很长一段时间都在纠结是否有必要好好学习它,但是始终都没有一个很好的理由说服自己.甚至是直到这个项目最终决定选用PostgreSQL时,我都没有真正意识到学习它的价值,当时只是想反正和其它数据库差不多,能用就行了.然而有一天晚上,自己也不知道为什么,躺在床上开始回想整个项目的实施过程,想着想着就想到了数据库选型这一问题上了.事情是这

PostgreSQL学习手册(常用数据类型)

PostgreSQL学习手册(常用数据类型) 一.数值类型:     下面是PostgreSQL所支持的数值类型的列表和简单说明: 名字 存储空间 描述 范围 smallint 2 字节 小范围整数 -32768 到 +32767 integer 4 字节 常用的整数 -2147483648 到 +2147483647 bigint 8 字节 大范围的整数 -9223372036854775808 到 9223372036854775807 decimal 变长 用户声明精度,精确 无限制 nu

PostgreSQL教程(十九)：SQL语言函数_PostgreSQL

一.基本概念:     SQL函数可以包含任意数量的查询,但是函数只返回最后一个查询(必须是SELECT)的结果.在简单情况下,返回最后一条查询结果的第一行.如果最后一个查询不返回任何行,那么该函数将返回NULL值.如果需要该函数返回最后一条SELECT语句的所有行,可以将函数的返回值定义为集合,即SETOF sometype.     SQL函数的函数体应该是用分号分隔的SQL语句列表,其中最后一条语句之后的分号是可选的.除非函数声明为返回void,否则最后一条语句必须是SELECT.事实上,

PostgreSQL学习手册(角色和权限)

原文地址:http://www.cnblogs.com/stephen-liu74/archive/2012/05/18/2302639.html   PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限.    一.数据库角色:     1. 创建角色:    CREATE ROLE role_name;        2.

《MATLAB/Simulink系统仿真超级学习手册》——2.2 MATLAB语言基本元素

2.2 MATLAB语言基本元素 MATLAB/Simulink系统仿真超级学习手册 MATLAB语言提供了丰富的数据类型,如实数.复数.向量.矩阵.字符串.多维数组.结构体.类和对象等,还提供了丰富的内置功能函数.这些功能使得MATLAB的编程功能非常强大.本节介绍变量和矩阵这两种最基本且常用的数据类型以及赋值语句的基本形式. 2.2.1 变量 变量是保存数据信息的一种最基本的数据类型.变量的命名应遵循如下规则: (1)变量名必须以字母开头: (2)变量名可以由字母.数字和下划线混合组成: (

《MATLAB信号处理超级学习手册》——1.4 本章小结

1.4 本章小结 MATLAB信号处理超级学习手册MATLAB 语言由于语法的简洁性.代码接近自然数学描述方式以及具有丰富的专业函数库等诸多优点,吸引了众多科学研究工作者,越来越成为科学研究.数值计算.建模仿真以及学术交流的事实标准. 本章主要介绍了MATLAB语言的8个特点,并举例说明了MATLAB语言的简洁.方便.然后从MATLAB用户界面.变量及赋值.绘图命令.MATLAB程序的结构流等四个方面说明MATLAB的基本操作.最后对于MATLAB中的重要内容M文件进行了简单介绍.

PostgreSQL SQL 语言：类型转换

本文档为PostgreSQL 9.6.0文档,本转载已得到原译者彭煜玮授权. 1. 概述 SQL是一种强类型语言.也就是说,每个数据项都有一个相关的数据类型,数据类型决定其行为和允许的用法. PostgreSQL有一个可扩展的类型系统,该系统比其它SQL实现更具通用和灵活.因而,PostgreSQL中大多数类型转换行为是由通用规则来管理的,而不是ad hoc启发式规则.这种做法允许使用混合类型表达式,即便是其中包含用户定义的类型. PostgreSQL扫描器/解析器只将词法元素分解成五个基本种类

PostgreSQL SQL 语言：并行查询

本文档为PostgreSQL 9.6.0文档,本转载已得到原译者彭煜玮授权. 1. 并行查询如何工作 当优化器判断对于某一个特定的查询,并行查询是最快的执行策略时,优化器将创建一个查询计划.该计划包括一个 Gather 节点.下面是一个简单的例子: EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%'; QUERY PLAN --------------------------------------------------

Oracle基础学习（三） Oracle PL／SQL语言基础

本章目标 PL/SQL语言基本结构 PL/SQL变量和常量的声明 PL/SQL程序的执行部分 了解PL/SQL常用函数 PL/SQL简介 1. PL/SQL简介 a) PL/SQL(Procedural Language/SQL,过程语言/SQL) i. 它是结合Oracle过程语言和结构化查询语言的一种扩展语言 ii. PL/SQL支持多种数据类型,可以使用条件语句和循环语句等控制结构 iii. PL/SQL可用于 创建存储过程.触发器和程序包,也可以用来处理业务规则.数据库事件或给SQL命令