首页 > 数据库 >PostgreSQL-用户定义的函数

PostgreSQL-用户定义的函数

时间：2023-07-09 23:22:36浏览次数：47  
标签：PostgreSQL 定义 int sum RETURNS SQL CREATE 函数

PostgreSQL-用户定义的函数

PostgreSQL是可扩展的，PostgreSQL服务器能够通过动态载入把用户编写的代码结合到自身中。也就是用户能够指定一个实现了新类型或函数的对象代码文件，并且PostgreSQL按要求载入它。

主要讲的是查询语言函数与过程语言函数中的PL/pgSQL(SQL过程语言)，其他函数只是概述。

用户定义的函数

PostgreSQL提供四种函数：

  • 查询语言函数(用SQL编写的函数)
  • 过程语言函数(用除SQL和C之外的语言编写的函数)
  • 内部函数(由C编写，此类函数被静态链接到PostgreSQL服务器中)
  • C语言函数(由C编写，此类函数被编译成动态载入对象，在PostgreSQL服务器需要时载入)
每一类函数可以采用基本类型、组合类型或者它们的组合作为参数。
每一类函数可以返回一个基本类型或一个组合类型，也可以返回一个基本类型或组合类型的集合。
允许函数重载，同一个名称用于多个不同函数，只有它们具有可区分的输入参数类型。
PostgreSQL允许用除SQL和c之外的语言编写函数，这些语言被称为过程语言(PL)。
动态载入是把内部函数与C语言函数区分开的特性，两者的编码习惯是一致的。

创建函数的SQL命令(CREATE FUNCATION)

CREATE [OR REPLACE] FUNCTION
函数名 ([参数模式] [参数名] 参数类型)
[RETURNS 返回类型 | RETURNS TABLE (列名 列类型)]
AS $$
  definition
$$ LANGUAGE 语言名;

CREATE FUNCTION :创建一个新函数
CREATE OR REPLACE FUNCTION :创建一个新函数或者替换一个现有的函数
参数模式 :IN、OUT、INOUT等等。如果省略，默认为IN。OUT参数不能和RETURNS一起使用
definition :一个定义该函数的字符串常量。可以是一个内部函数名、一个对象文件的路径、一个SQL命令或者用一种过程语言编写的文本

查询语言函数(SQL函数)

SQL函数的主体必须是一个有分号(;)分隔的SQL语句的列表。
SQL函数被声明返回void，最后一个语句必须是一个SELECT或者一个带有RETURNING子句的INSERT、UPDATE、DELETE。
SQL函数的返回类型可以通过SETOF xx 或者 RETURNS TABLE() 方式声明返回一个集合(多个行)

SQL函数的参数

一个SQL函数的参数可以在函数体中用名称或编号引用

名称的方式 : 
CREATE FUNCTION add_test1(a int,b int)
RETURNS int
AS $$
SELECT a + b;
$$ LANGUAGE SQL;

编号引用
CREATE FUNCTION add_test2(int,int)
RETURNS int
AS $$
SELECT $1 + $2;
$$ LANGUAGE SQL;

基本类型的SQL函数

无参函数

CREATE FUNCTION print_hello()
RETURNS text
AS $$
SELECT 'HELLO';
$$ LANGUAGE SQL;

有参函数

CREATE FUNCTION print_input_text(content text)
RETURNS text
AS $$
SELECT content;
$$ LANGUAGE SQL;

无返回值函数

CREATE FUNCTION no_return_test()
RETURNS void
AS $$
SELECT 'NO MEANING';
$$ LANGUAGE SQL;

带有输出参数的SQL函数

单个输出参数

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

多个输出参数

CREATE FUNCTION add_n_test(IN x int, IN y int, OUT sum1 int, OUT sum2 int)
AS $$
SELECT x + x , y + y;
$$ LANGUAGE SQL;

组合类型上的SQL函数

在使用组合类型作为参数时，需要指定参数的属性(域)

前提

新增一个emp表作为输入的组合类型

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

INSERT INTO emp VALUES ('Sam', '5000', 45);
INSERT INTO emp VALUES ('James', '4000', 39);

组合类型作为参数

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

SELECT name , double_salary(emp.*) from emp;

注： 
$1.xx :需要使用行值的一个域，如 $1.salary
table_name.* :表示用一个表的整个当前行作为一个组合值

组合类型作为返回值

CREATE FUNCTION new_emp()
RETURNS emp
AS $$
SELECT text 'test_name' AS name,
1000.0 AS salary,
35 AS age;
$$ LANGUAGE SQL;

将函数返回值作为一个值
select new_emp();
将函数返回值作为一个表
select (new_emp()).*;
select * from new_emp();

返回集合的SQL函数

当一个SQL函数被声明返回SETOF xx 时，该函数的最后一个查询执行完，会输出每一行

前提

新增一个foo表和tab表用于返回集合

CREATE TABLE foo (fooid int, foosubid int, fooname text);
INSERT INTO foo VALUES (1, 1, 'Joe');
INSERT INTO foo VALUES (1, 2, 'Ed');
INSERT INTO foo VALUES (2, 1, 'Mary');

CREATE TABLE tab (y int, z int);
INSERT INTO tab VALUES (1, 2), (3, 4), (5, 6), (7, 8);

返回所有表数据

CREATE OR REPLACE FUNCTION display_foo(int)
RETURNS SETOF foo
AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

select * from display_foo(1);

返回带有输出参数定义的列

CREATE FUNCTION sum_tab(x int, OUT sum1 int, OUT sum2 int)
RETURNS SETOF record
AS $$
SELECT $1 + tab.y, $1 + tab.y FROM tab;
$$ LANGUAGE SQL;

select * from sum_tab(10);

返回TABLE的SQL函数

RETURNS TABLE (列名 列类型)，此种方式与SETOF record等效

CREATE OR REPLACE FUNCTION sum_tab(x int)
RETURNS TABLE(sum1 int, sum2 int)
AS $$
SELECT $1 + tab.y, $1 + tab.y FROM tab;
$$ LANGUAGE SQL;

select * from sum_tab(10);

过程语言函数

PostgreSQL运行处理SQL和C之外的其他语言编写用户定义的函数。这些其他语言通常被称为过程语言(PL)，是一种用于PostgreSQL数据库系统的可载入的过程语言。并不内建在PostgreSQL服务器中，通过可装载模块提供

在PostgreSQL的标准发布中有四种过程语言可用：

  • PL/pgSQL(SQL过程语言):用SQL语言编写函数
  • PL/Tcl(Tcl过程语言):用Tcl语言编写函数
  • PL/Perl(Perl过程语言):用Perl语言编写函数
  • PL/Python(Python过程语言):用Python编写函数

PL/pgSQL(SQL过程语言)

PL/pgSQL的设计目的是创建一种这样的可载入过程语言

  • 可以被用来创建函数和触发器过程
  • 对SQL语言增加控制结构
  • 可以执行复杂计算
  • 继承所有用户定义类型、函数、操作符
  • 可以被定义为受服务器信任
  • 便于使用

注:

PostgreSQL9.0之后的版本，PL/pgSQL是默认被安装的

通过PL/pgSQL可以使用SQL中所有的数据类型、操作符、函数

PL/pgSQL的结构

函数的结构:

CREATE FUNCTION 函数名(参数名 参数类型)
RETURNS 返回类型
AS $$
 函数体
$$ LANGUAGE plpgsql;

PL/pgSQL是一种块结构的语言。一个函数体的完整文本必须是一个块。

一个块的定义:

$$
[DECLARE 声明语句]
BEGIN
	语句
END;
$$

声明：

DECLARE 
	变量名 变量类型 := 表达式;
	
eg：创建一个函数，声明两个text变量
CREATE FUNCTION display_sum_text(a text, b text)
RETURNS text
AS $$
DECLARE
	local_a text := a;
	local_b text := b;
BEGIN
	RETURN local_a || local_b;
END;
$$ LANGUAGE plpgsql;

注：
':=' 为赋值字符
'||' 为字符串拼接字符

PL/pgSQL的控制结构

从一个函数返回(RETURN)

RETURN 表达式

eg: 创建一个函数，返回两个数值类型的和
CREATE OR REPLACE FUNCTION sum_test(a int, b int)
RETURNS int
AS $$
DECLARE
	sum int := 0;
BEGIN
	sum := a + b;
	RETURN sum;
END;
$$ LANGUAGE plpgsql;

条件-IF

IF有三种形式
IF ... THEN ... END IF
IF ... ELSE ... END IF
IF ... THEN ... ELSIF ...THEN ... ELSE ...END IF

eg: 创建一个函数，判断一个数值类型的大小
CREATE OR REPLACE FUNCTION judge_num(num int)
RETURNS text
AS $$
DECLARE 
	result_num text := '';
BEGIN
	IF num > 0 THEN 
		result_num := '大于0';
	ELSIF num = 0 THEN
		result_num := '等于0';
	ELSE
		result_num := '小于0';
	END IF;
	RETURN result_num;
END;
$$ LANGUAGE plpgsql;

条件-CASE

CASE有两种形式
简单CASE
CASE 搜索表达式
	WHEN 表达式 THEN
		语句
	WHEN 表达式 THEN
		语句
	ELSE
		语句
END CASE;
搜索CASE
CASE
	WHEN 判断表达式 THEN
		语句
	WHEN 判断表达式 THEN
		语句
	ELSE
		语句
END CASE;

eg: 创建一个函数，判断分数
CREATE FUNCTION judge_score(score numeric)
RETURNS text
AS $$
BEGIN
	CASE 
		WHEN score < 60 THEN
			RETURN '未及格';
		ELSE
			RETURN '及格';
	END CASE;
END;
$$ LANGUAGE plpgsql;

循环-LOOP

...
LOOP
	语句
END LOOP
...

LOOP定义一个无条件的循环，它会无限重复直到被 EXIT 或 RETURN 语句终止

EXIT ... [WHEN 判断表达式] 

EXIT 此层循环被终止，如果指定了WHEN则在判断表达式为真时退出循环

CONTINUE ... [WHEN 判断表达式]

CONTINUE 此层循环的下一次迭代开始，如果指定了WHEN则在判断表达式为真时进行下一次迭代

eg: 创建一个函数，计算1-10之间的奇数之和
CREATE FUNCTION sum_odd_num()
RETURNS int
AS $$
DECLARE
	odd_sum int :=  0;
	i		int := 0;
BEGIN
	LOOP
		i := i + 1;
		EXIT WHEN i > 10;
		CONTINUE WHEN mod(i,2) = 0;
		odd_sum := odd_sum + i;
	END LOOP;
	RETURN odd_sum;
END;
$$
LANGUAGE plpgsql;

注:
mod(a,b): 取模函数，计算a/b的余数

循环-FOR

...
FOR 变量名 IN [REVERSE] 表达式 .. 表达式 [ BY 表达式]
LOOP
	语句
END LOOP;
...

FOR 会创建一个在一个整数范围上迭代的循环，变量名自动定义为integer类型。给出范围的上下界的两个表达式在进入循环时计算一次，如果没有指定BY子句，迭代步长默认为1，如果没有指定REVERSE,那么每次迭代后步长值会被增加

eg: 创建一个函数，计算1-10之间的偶数之和
CREATE FUNCTION sum_even_num()
RETURNS int
AS $$
DECLARE
	even_sum int :=  0;
	i		int := 0;
BEGIN
	FOR i IN REVERSE 10 .. 1 BY 2
	LOOP
		even_sum := even_sum + i;
	END LOOP;
	RETURN even_sum;
END;
$$
LANGUAGE plpgsql;

循环-FOREACH(数组循环)

...
FOREACH target IN ARRAY 表达式
LOOP
	语句
END LOOP;
...

target变量会被逐一赋予数组每一个元素值

eg: 创建一个函数，计算数组之和
CREATE FUNCTION sum_array()
RETURNS int
AS $$
DECLARE
	arr1 int[] := array[1,2,3];
	i 	 int;
	array_sum int := 0;
BEGIN
	FOREACH i IN ARRAY arr1
	LOOP
		array_sum := i + array_sum;
		RAISE NOTICE 'row = %', i;
	END LOOP;
	RETURN array_sum;
END;
$$ LANGUAGE plpgsql;

注:
RAISE NOTICE 报告消息，在执行会报告给客户端

循环-WHILE

...
WHILE 判断表达式 LOOP
	语句
END LOOP
...

只要判断表达式为真，就会重复语句序列，每次进入到循环体之前都会检查判断表达式

eg: 创建一个函数，计算1-10数值之和
CREATE OR REPLACE FUNCTION sum_num()
RETURNS int
AS $$
DECLARE 
	num_sum int DEFAULT 0;
	i int DEFAULT 1;
BEGIN
	WHILE i<=10
	LOOP
		num_sum := num_sum +i;
		i := i+1;
	END LOOP;
	RETURN num_sum;
END;
$$ LANGUAGE plpgsql;

标签：PostgreSQL,定义,int,sum,RETURNS,SQL,CREATE,函数
From： https://www.cnblogs.com/shenStudy/p/17539659.html

相关文章

  • 「高等数学」1.1.2 函数
    函数的概念定义:设数集\(D\subset\mathbf{R}\),则称映射\(f:D\rightarrow\mathbf{R}\)为定义在\(D\)上的函数,通常简记为\[y=f(x),x\inD,\]其中\(x\)称为自变量,\(y\)成为因变量,\(D\)称为定义域,记作\(D_f\),即\(D_f=D\).函数的定义中,对于每......
  • 高等数学——函数
    函数定义设数集\(D\subset\text{R}\)，则称映射\(f:D\to\text{R}\)为定义在\(D\)上的函数，通常简记为：\[y=f(x),x\inD\]其中\(x\)称为自变量，\(y\)称为因变量，\(D\)称为定义域，记作\(D_{f}\)，即\(D_{f}=D\)，值域\(R_{f}=f(D)\)。每个\(x\inD\)，都有唯一......
  • watchEffect函数
    watch的套路是：既要指明监视的属性，也要指明监视的回调。watchEffect的套路是：不用指明监视哪个属性，监视的回调中用到哪个属性，那就监视哪个属性。watchEffect有点像computed：但computed注重的计算出来的值（回调函数的返回值），所以必须要写返回值。而watchEffect更注重的是......
  • [C/C++] 函数
    疑问1、函数结束后，函数栈释放的内容有哪些？2、通过函数修改形参的值怎么实现？值传递还是引用传递？基本类型、数组、结构体有什么区别？3、如果想通过函数对实参进行malloc,为什么必须用二级指针？函数栈空间在一个函数执行完毕后其所占用的内存空间（除了静态和全局变量）统统会被释放......
  • watch函数
    与Vue2.x中watch配置功能一致两个小“坑”：监视reactive定义的响应式数据时：oldValue无法正确获取、强制开启了深度监视（deep配置失效）。监视reactive定义的响应式数据中某个属性时：deep配置有效。//情况一：监视ref定义的响应式数据watch(sum,(newValue,oldValue)=>{......
  • isinstance() 函数
     文章目录一、isinstance()函数1.1用途描述1.2使用方法1.3案例分析 一、isinstance()函数  下面从用途描述、使用方法、案例分析、三个方面进行讲解。1.1用途描述  isinstance()函数来判断一个对象是否是一个已知的类型，类似type()。1.2使用方......
  • 内置函数reduce
    1'''2filter()函数是Python内置的一个高阶函数，它用于过滤可迭代对象中的元素，只保留满足特定条件的元素。filter()函数接受两个参数：一个函数和一个可迭代对象。3语法：4filter(function,iterable)5其中：61.function是一个函数，它接受一个参数，并返回一个布......
  • PostgreSQL NUMERIC 数据类型
    基本介绍NUMERIC类型的语法：NUMERIC(precision,scale)precision表示整个数据长度，scale表示小数部分的长度。如：1234.567，precision为7，scale为3.NUMERIC类型在小数点前面长度可达到**131,072**，小数点后面长度可达到16,383。scale>=0，下面示例表示scale为0：NUME......
  • R语言 ggplot函数中 annotate选项增加注释
     001、基础绘图ggplot(data=mtcars,aes(x=mpg,y=disp,color=factor(cyl)))+geom_point()##基础绘图 002、annotete在任意位置增加注释ggplot(data=mtcars,aes(x=mpg,y=disp,##在坐标，25，300处增加QQcolor=factor(cyl)))+geom_point......
  • freeRTOS 10.0.1 的xQueueReceive 函数bug
    xQueueReceive读取队列后,如果再次读取消息队列并保存到同一个变量中,那么还可以读到值 读取后,再读取一次,还有值 必须要手动清除该变量,或者用一个新的指针接收,才会读到0 举例:手动清楚该变量,再读取就是0 要么就是用一个新的变量来接收,这样也可以读到0  ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99