PostgreSQL之“SQL语言函数”

发布时间:2018-12-20  栏目:sqlite  评论:0 Comments

同等、基本概念:

平等、基本概念:

   
SQL函数可以涵盖自由数量之询问,但是函数只回最终一个查询(必须是SELECT)的结果。在简约境况下,重临最终一修查询结果的首先行。假诺最后一个询问不回来外履行,那么该函数将回来NULL值。假若要该函数回最终一长长的SELECT语句的富有执行,可以用函数的回到值定义为会聚,即SETOF
sometype。

   
SQL函数可以分包自由数量之询问,不过函数只回去最后一个查询(必须是SELECT)的结果。在简易情状下,重返最终一漫长查询结果的第一实践。如若最后一
个查询不回去外履行,那么该函数将回NULL值。假如用该函数回最终一久SELECT语句之持有执行,可以用函数的回值定义为集,即SETOF
sometype。

   
SQL函数的函数体应该是故分号分隔的SQL语句列表,其中最终一长达语句之后的支行是可选的。除非函数注解也回去void,否则最终一长告句子必须是SELECT。事实上,在SQL函数中,不仅可涵盖SELECT查询语句,也可以蕴涵INSERT、UPDATE和DELETE等其余专业的SQL语句,不过与物相关的报句不能包含其中,如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。

   
SQL函数的函数体应该是为此分号分隔的SQL语句列表,其中最终一长达语句之后的分公司是可选的。除非函数声明也回去void,否则最后一长条告句必须是
SELECT。事实上,在SQL函数中,不仅可以分包SELECT查询语句,也可涵盖INSERT、UPDATE和DELETE等其他标准的SQL语
句,然则跟东西相关的告知句不可知包含其中,如BEGIN、COMMIT、ROLLBACK和SAVEPOINT等。

    CREATE
FUNCTION命令的语法要求部数体写成一个字符串文本。平时来说,该文本字符串常量使用日元入($$)围住,如:
 

    CREATE
FUNCTION命令的语法要求部数体写成一个字符串文本。平时来说,该文本字符串常量使用港币入($$)围住,如:
 

复制代码 代码如下:

复制代码 代码如下:

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

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

   
最终索要表达的凡SQL函数中的参数,PostgreSQL定义$1表示第一只参数,$2为第二独参数并由此类推。倘使参数是复合类型,则可以使用点表示法,即$1.name拜访复合类型参数中之name字段。需要小心的凡函数参数只好当数据值,而非可知用来标识符,如:
 

   
最终要验证的是SQL函数中之参数,PostgreSQL定义$1表示第一独参数,$2也次个参数并因而类推。假若参数是复合类型,则足以使用点表示
法,即$1.name访复合类型参数中的name字段。需要专注的是函数参数只好当数据值,而休可以用于标识符,如:
 

复制代码 代码如下:

复制代码 代码如下:

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

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

老二、基本类型:
    
    最简便易行的SQL函数可能就是从未参数都重回基本类型的函数了,如:
 

老二、基本型:
    
    最简单易行的SQL函数可能就从未参数还重返基本型的函数了,如:
 

复制代码 代码如下:

复制代码 代码如下:

    CREATE FUNCTION one() RETURNS integer AS $$
        SELECT 1 AS result;
    $$ LANGUAGE 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)
 

    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语句,它们之间是用分号进行分隔的。
 

   
在脚的事例中,函数体内含多独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;

    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). 成立数据表,这样和的相应的复合类型也随后变动。
 

    1). 创设数据表,这样和的相应的复合类型为随着转移。
 

复制代码 代码如下:

复制代码 代码如下:

    CREATE TABLE emp (
        name        text,
        salary      numeric,
        age         integer,
    );manbet手机客户端3.0,
 

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

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

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

复制代码 代码如下:

复制代码 代码如下:

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

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

    3). 在select语句被,可以利用emp.*表示emp表的一整行数额。
 

    3). 在select语句被,可以动用emp.*代表emp表的一整行数。
 

复制代码 代码如下:

复制代码 代码如下:

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

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

    4). 大家呢能够使用ROW表明式构造自定义之复合类型,如:
 

    4). 大家也堪运用ROW表达式构造自定义的复合类型,如:
 

复制代码 代码如下:

复制代码 代码如下:

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

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

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

    5). 创设一个函数,其归来回值为复合类型,如:
 

复制代码 代码如下:

复制代码 代码如下:

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

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

    6). 调用再次回到复合类型的函数。
 

    6). 调用再次来到复合类型的函数。
 

复制代码 代码如下:

复制代码 代码如下:

    SELECT new_emp();
 

    SELECT new_emp();
 

    7). 调用再次来到复合类型的函数,同时做客该再次回到值的某部字段。
 

    7). 调用重返复合类型的函数,同时做客该再次来到值的某字段。
 

复制代码 代码如下:

复制代码 代码如下:

    SELECT (new_emp()).name;
 

    SELECT (new_emp()).name;
 

季、带输出参数的函数:

季、带输出参数的函数:

    还有同种方法好用来重临函数执行之结果,即出口参数,如:
 

    还有同栽方法可以用来重临函数执行之结果,即出口参数,如:
 

复制代码 代码如下:

复制代码 代码如下:

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

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

    调用方法以及归纳果与add_em(带有再次来到值的函数)完全一致,如:
 

    调用方法以及归结果和add_em(带有重临值的函数)完全一致,如:
 

复制代码 代码如下:

复制代码 代码如下:

    SELECT add_em(3,7);
   

    SELECT add_em(3,7);
   

   
那个蕴含输出参数的函数和事先的add_em函数没有实质的区分。事实上,输出参数的的确价值在它们吧函数提供了回多单字段的门径。如,
 

   
那些蕴含输出参数的函数和前的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;
 

    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)
 

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

   
在面的言传身教中,IN用于表示该函数参数为输入参数(缺省值,可以忽略),OUT则象征该参数为出口参数。

   
在点的言传身教中,IN用于表示该函数参数为输入参数(缺省值,可以忽略),OUT则意味着该参数为出口参数。

五、再次来到结果作表数据源:

五、重临结果当表数据源:

   
所有SQL函数都可以当查询的FROM子句里使用。该方法对再次来到复合类型的函数而言特别有由此,假诺该函数定义也回到一个着力项目,那么该函数生成一个字眼段表,如若该函数定义也回到一个复合类型,那么该函数生成一个复合类型里每个属性组成的推行。见如下示例:
    1). 创造一个数据表。
 

   
所有SQL函数都得当查询的FROM子句里使用。该办法对重临复合类型的函数而言特别有因而,如若该函数定义也回到一个骨干型,那么该函数生成一个字段表,假设该函数定义为回去一个复合类型,那么该函数生成一个复合类型里每个属性组成的尽。见如下示例:
    1). 创立一个数据表。
 

复制代码 代码如下:

复制代码 代码如下:

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

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

    2). 创设SQL函数,其回来回值为跟foo表对应之复合类型。
 

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

复制代码 代码如下:

复制代码 代码如下:

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

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

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

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

复制代码 代码如下:

复制代码 代码如下:

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

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

六、重回集合的SQL函数:

六、重临集合的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函数的重回值为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). 函数参数和归值都为多态类型。
 

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

复制代码 代码如下:

复制代码 代码如下:

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

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

   
其调用情势与调用此外类型的SQL函数完全一致,只是于传递字符串类型的参数时,需要显式转换来对象项目,否则将会见受视为unknown类型,如:
 

   
其调用模式跟调用其余连串的SQL函数完全等同,只是于传递字符串类型的参数时,需要显式转换来对象项目,否则将相会受视为unknown类型,如:
 

复制代码 代码如下:

复制代码 代码如下:

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

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

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

    2). 函数的参数为多态类型,而回值则也主干项目。
 

复制代码 代码如下:

复制代码 代码如下:

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

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

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

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

复制代码 代码如下:

复制代码 代码如下:

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

    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 …

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

   
由于当PostgreSQL中函数补助重载,因而当剔除函数时,也必须指定参数列表,如:
 

   
由于在PostgreSQL中函数帮助重载,由此在去函数时,也得指定参数列表,如:
 

复制代码 代码如下:

复制代码 代码如下:

    DROP FUNCTION test(int, real);
    DROP FUNCTION test(smallint,double);
 

    DROP FUNCTION test(int, real);
    DROP FUNCTION test(smallint,double);

公可能感兴趣的篇章:

留下评论

网站地图xml地图