文档章节

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

Junn
 Junn
发布于 2016/01/07 10:58
字数 1704
阅读 114
收藏 0
一、基本概念:

    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); 

本文转载自:http://www.cnblogs.com/stephen-liu74/archive/2012/01/04/2312194.html

Junn
粉丝 145
博文 412
码字总数 288918
作品 0
海淀
技术主管
私信 提问
PostgreSQL 与 MS SQL(SQL Server) 类型映射关系

标签 PostgreSQL , ms sql , SQL Server , 类型映射 背景 PostgreSQL与SQL Server的类型映射: 1、常用类型映射 https://www.codeproject.com/tips/1068276/convert-sql-server-database-to-......

德哥
04/14
0
0
PostgreSQL 物化视图(Oracle同步到PG,PG同步到PG) - by pgsnapshot (plperlu trigger) (支持类似Oracle的mvlog fast complete force刷新)

标签 PostgreSQL , 物化视图 , 增量刷新 , mvlog , Oracle 同步到 PG , PG 同步到 PG 背景 PostgreSQL自身的物化视图没有MVLOG,也就是说,刷新的时候是VIEW定义产生的记录与MV已刷新的记录进...

德哥
04/27
0
0
科普一种可以将PG变成通用SQL引擎的技术

作者介绍 伊翼,网名“小wing”,野生PG爱好者,从事数据库相关工作已近十年,目前供职于全球最大的通讯设备供应商。 原标题:《当FDW遇上GO》 FDW(Foreign Data Wrapper)是PostgreSQL(下...

伊翼
2018/06/14
0
0
PostgreSQL和MySQL

翻译来源:https://www.2ndquadrant.com/en/postgresql/postgresql-vs-mysql/ PostgreSQL和MySQL 之间有着根本的区别。在评估两个系统之间的差异和折衷之后,必须做出明智的决定。 我们已经提...

悟道之客
2018/05/04
0
0
PostgreSQL学习手册(十一) 数据库管理

一、概述: 数据库可以被看成是SQL对象(数据库对象)的命名集合,通常而言,每个数据库对象(表、函数等)只属于一个数据库。不过对于部分系统表而言,如 pg_database,是属于整个集群的。更准确...

长平狐
2012/09/06
112
0

没有更多内容

加载失败,请刷新页面

加载更多

我为什么要写微信公众号

埋一颗种子,细心呵护,静待她枝繁叶茂,葱郁参天 V2论坛上有个帖子【做程序员最重要的还是一定要有自己的作品】,作者写道: 能有一个作品和你的名字联系在一起,应当成为在职业生涯前期着意...

运维咖啡吧
24分钟前
2
0
数据库

数据库架构 数据库架构可以分为存储文件系统和程序实例两大块,而程序实例根据不同的功能又可以分为如下小模块。 1550644570798 索引模块 常见的问题有: 为什么要使用索引 什么样的信息能成...

一只小青蛙
今天
5
0
PHP常用经典算法实现

<? //-------------------- // 基本数据结构算法 //-------------------- //二分查找(数组里查找某个元素) function bin_sch($array, $low, $high, $k){ if ( $low <= $high){ $mid = int......

半缘修道半缘君丶
昨天
5
0
GIL 已经被杀死了么?

本文原创并首发于公众号【Python猫】,未经授权,请勿转载。 原文地址:https://mp.weixin.qq.com/s/8KvQemz0SWq2hw-2aBPv2Q 花下猫语: Python 中最广为人诟病的一点,大概就是它的 GIL 了。...

豌豆花下猫
昨天
6
0
git commit message form

commit message一般包括3部分:Header、Body、Footer。 <type>(<scope>):<subject>blank line<body>blank line<footer> header是必需的,body、footer可以省略。 header中type、subject......

ninjaFrog
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部