• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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);
     

    您可能感兴趣的文章:
    • PostgreSQL中的XML操作函数代码
    • PostgreSQL 正则表达式 常用函数的总结
    • 深入解读PostgreSQL中的序列及其相关函数的用法
    • PostgreSQL教程(七):函数和操作符详解(3)
    • PostgreSQL教程(五):函数和操作符详解(1)
    • PostgreSQL教程(六):函数和操作符详解(2)
    • PostgreSQL Node.js实现函数计算方法示例
    • PostgreSQL数据库中窗口函数的语法与使用
    上一篇:PostgreSQL教程(十八):客户端命令(2)
    下一篇:PostgreSQL教程(二十):PL/pgSQL过程语言
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    PostgreSQL教程(十九):SQL语言函数 PostgreSQL,教程,十九,SQL,语言,