PL/pgSQL编写postgresql函数(一)--声明语法

初识函数

create or replace function somefunc() returns integer as

$$

declare

quantity integer := 30;

begin

raise notice 'Quantity1 here is %',quantity;

quantity := 50;

--创建一个子块

declare

quantity integer := 80;

begin

raise notice 'Quantity2 here is %',quantity;

end;

raise notice 'Quantity3 here is %',quantity;

return  quantity;

end 

$$

language plpgsql

 

思考：raise notice依次打印出什么结果？？？？

 

 

 

 

 

 

结果：

NOTICE:  00000: Quantity1 here is 30

NOTICE:  00000: Quantity2 here is 80

NOTICE:  00000: Quantity3 here is 50

 

 

 

1、声明语法

1.1、函数声明语法

1.1.1 结构定义

CREATE [ OR REPLACE ] FUNCTION  funcname([IN parameters,OUT parameters]) RETURNS  return_value_type AS

$body$

DECLARE

declaration;

[...]

BEGIN

 statement   --逻辑块（pl/pgsql代码块）

[ RETURN return_value;]

END;

$body$

LANGUAGE plpgsql

1.1.2 关键字说明

CREATE [ OR REPLACE ] FUNCTION 创建函数关键字

  IN   表示该参数为输入参数

OUT 表示该参数为输出参数

DECLARE  定义变量列表

BEGIN    正文代码开始

RETURN  返回值关键字

END     正文代码结束

LANGUAGE 指定编程语言

1.1.3参数说明

funcname 函数名

parameters为参数（由 参数名 类型组成，如v_id int）

declaration 函数变量列表（变量名 类型）   

return_value_type  返回值类型（无返回值使用void）

$body$ 表示里面引用的内容是函数逻辑块

return_value  返回值变量

注意：

A. 输入参数可以有多个，输出参数也可以为多个。

B. 函数参数中使用OUT标明返回参数或者return_value_type为void时，函数逻辑块中不需要使用return进行值返回，如果没有使用OUT且return_value_type不为void时，必须在逻辑块中使用return表明返回值，无可写成return null。

C. OUT指定的返回值类型或者return指定的返回值类型必须与returns后指定的类型一致。

例子：

create table t_test(id serial,name varchar,age int,code text);

insert into t_test(name,age,code) values('张三',18,'10010-11'),

('李四',20,'10010-12'),

('王五',22,'10010-13');

函数：

create or replace function t_func(in v_id int,out v_name varchar)

returns varchar as 

$$

begin

select name into $2 from t_test where id=$1;

end 

$$

language plpgsql

 

如上函数也可改写为：

create or replace function t_func( v_id int)

returns varchar as 

$$

declare

v_name varchar;

begin

select name into v_name from t_test where id=$1;

return v_name; 

end 

$$

language plpgsql

函数参数没有使用IN 或者 OUT表名时，缺省为输入参数。

1.2、参数传递与别名

在postgresql函数中，传递给函数的参数被命名为$1、$2等等。为了增强$n的可读性，我们可以为参数声明别名。

我们可以通过两中方式来声明参数别名（显式声明与隐式声明），不同的定义方法，参数的调用方法也不一样。

当然，无论是显式声明还是隐式声明，都可以通过使用$n来进行参数传递，如上述函数例子中的$1。

1.2.1、显式声明

显示声明就是在CREATE FUNCTION 命令中为参数给定一个名称，例如

create function fn_test1(total int) returns int as

$$

begin

return total * 2;

end 

$$

language plpgsql;

如上，为输入参数给定名称为total，在函数中可以直接使用total来调用该参数进行运算。当然此处也可以将total*2编写成$1*2。

 

​​​​​​​1.2.2、隐士声明

我们可以通过在函数declare块中使用 name ALIAS FOR $n 来为参数声明别名，关键字为ALIAS FOR。如下

create function fn_test2( int) returns int as

$$

declare

subtotal ALIAS FOR $1;

begin

return subtotal * 2;

end 

$$

language plpgsql;

 

 

​​​​​​​1.3、变量/常量声明

1.3.1 语法

函数中在declare块中声明变量语法为

variable_name [ CONSTANT ] data_type  [ NOT NULL ] [ { DEFAULT | := } value ] ;

其中：

variable_name 为变量/常量名称

CONSTANT      关键字标识为常量（值不可修改）

data_type     变量/常量类型

NOT NULL       约束该变量/常量初始值不能为空（声明时就需要有default值）

DEFAULT       设置默认值

:=            赋值运算符（设置默认值）

例子如下：

create or replace function fn_test4() returns void as

$$

declare 

v_value1 varchar;

v_value2 int:= 100 ;

v_value3 varchar default '瀚高3';

v_value4 varchar  not null  := '瀚高4' ;

v_value5 varchar not null default '瀚高5' ;

begin

raise notice 'v_value1=%',v_value1;

raise notice 'v_value2=%',v_value2;

raise notice 'v_value3=%',v_value3;

raise notice 'v_value4=%',v_value4;

raise notice 'v_value5=%',v_value5;

end

$$

language plpgsql

执行打印结果：

test_db=# select fn_test4();

NOTICE:  00000: v_value1=<NULL>

NOTICE:  00000: v_value2=100

NOTICE:  00000: v_value3=瀚高3

NOTICE:  00000: v_value4=瀚高4

NOTICE:  00000: v_value5=瀚高5

 fn_test4

----------

(1 行记录)

1.3.2 错误示例

如下属于错误定义

有not null约束无默认值，且没有给该变量赋值

创建函数不会报错，但调用时会报错误。

注意：not null，千万不要写成notnull，否则会当成判断语句执行，出现如下效果：

create or replace function fn_test4() returns void as

$$

declare 

v_value varchar :='张三' notnull;

begin

raise notice 'v_value=%',v_value;

end

$$

language plpgsql

test_db=# select fn_test4();

NOTICE:  00000: v_value=true

 fn_test4

----------

(1 行记录)

可以看到打印出来的v_value值为true，但我们明明给v_value赋值为“张三”。由于解析器当成判断语句执行，所以当v_value有初始值，判断出v_value的值不为null时，结果为true。

1.4、其他声明语法

​​​​​​​1.4.1、拷贝类型

将变量类型声明为数据库中已有列数据类型。

声明语法 v_value  variable%TYPE

例子 v_user_id  user.user_id%TYPE    将v_user_id变量数据类型声明为user表中user_id的数据类型。

create or replace function fn_type(v_id int) returns int as 

$$

declare

v_user_id t_test.id%type;

    begin

    select id from t_test where id=v_id into v_user_id;

return v_user_id;

    end 

$$

language plpgsql

使用拷贝类型的好处是，在多态函数中不需要知道实际引用的数据结构，如果引用项被改变，不需要改变函数定义。

​​​​​​​1.4.2、行类型

一个组合类型的变量称为一个行变量。通常用来接收select或for查询结果的一个整行。

声明语法  v_value  table_name%ROWTYPE

              v_value  composite_type_name;

例子：

create type v_user as (id int,name  varchar,code text);  --定义一个组合类型

create or replace function fn_rowtype(v_id int) returns text as 

$$

declare

v_user1 t_test%ROWTYPE;   --定义为表行数据类型

v_user2  v_user;    --定义为已有组合类型

    begin

    select id,name,age into v_user1 from t_test where id=v_id ;

select id,name,code into v_user2 from t_test where id=v_id ;

raise notice 'v_user1=%',v_user1;

raise notice 'v_user2=%',v_user2;

return v_user1.id || v_user1.name ||v_user1.age||'--------'||v_user2.id || v_user2.name ||v_user2.code;

    end 

$$

language plpgsql

1.4.3、记录类型

记录类型和行类型类似，但行累心各有预定义结构，但记录类型没有预定义结构，它是根据select或for命令期间为其赋值的行的真实结构。

记录类型RECORD并非一个真实数据类型，它是一个占位符，随着赋值的行结构动态变化。

声明语法   v_value RECORD

例子  

create or replace function fn_record(v_id int) returns text as 

$$

declare

v_record  RECORD;   

 

    begin

    select id,name,age into v_record from t_test where id=v_id ;

raise notice 'v_record=%',v_record;   --1号位

select id,name,code into v_record from t_test where id=v_id ;

raise notice 'v_record=%',v_record;   --2号位

return v_record.id ||v_record.name;

    end 

$$

language plpgsql

我们可以看到打印结果如下，可以看到v_record 是变化的。

test_db=# select fn_record(1);

NOTICE:  00000: v_record=(1,张三,18)    --1号位

NOTICE:  00000: v_record=(1,张三,10010-11)  --2号位

 fn_record

-----------

 1张三

(1 行记录)