plpgsql系列教程(2)-变量声明与数据类型

本文由 @小刘先森 原创，转载请注明出处。

PL/pgSQL系列文章，方便小伙伴们学习。

PL/pgSQL从入门到放弃(1)-入门

PL/pgSQL从入门到放弃(2)-变量定义与数据类型

PL/pgSQL从入门到放弃(3)-函数

PL/pgSQL从入门到放弃(4.1)-函数返回常用数据类型——组合数据类型

PL/pgSQL从入门到放弃(4.2)-函数返回常用数据类型——行数据类型

PL/pgSQL从入门到放弃(4.3)-函数返回常用数据类型——记录数据类型

PL/pgSQL从入门到放弃(4.4)-函数返回常用数据类型——复制数据类型

PL/pgSQL从入门到放弃(5)-条件语句与循环语句

PL/pgSQL从入门到放弃(6)-游标

声明变量

上一篇介绍到，PL/pgSQL是块结构的语言。在一个块中用到的所有变量必须在

DECLARE

下进行声明。

唯一的例外是在一个整数范围上迭代的FOR循环变量会被自动声明为一个整数变量，并且相似地在一个游标结果上迭代的FOR循环变量会被自动地声明为一个记录变量。

声明变量

声明变量的语法为：

name [ CONSTANT ] type [ COLLATE collation_name ] [ NOT NULL ] [ { DEFAULT | := | = } expression ];

CREATE FUNCTION test() RETURNS void AS $$
<< outerblock >>
DECLARE
    outer_name varchar; --无默认值
    user_id integer := 1; --默认值1
BEGIN
    -- 创建一个子块
    DECLARE
        inter_name varchar := '我是子块的变量';
    BEGIN
    END;
    RETURN name;
END;
$$ LANGUAGE plpgsql;
           

以上在

outerblock

块中声明了两个变量（outer_name和user_id默认值为1），在子块中声明了一个变量（inter_name）

数据类型

以下列举常用的数据类型以及变量声明示例。详细可以参考PostgreSQL官网或者PostgreSQL中文翻译。

基本数据类型

• 数值类型

DECLARE
    quantity1 numeric;      --任何精度和比例的数字值
    quantity2 numeric(4);   --4个精度的整数
    quantity3 numeric(6,2); --6个精度的数字，小数点后两位
           

• 整数类型

DECLARE
    user_id1 smallint/int2; --2字节整数
    user_id2 integer/int4;  --4字节整数（典型选择）
    user_id3 bigint/int8;   --8字节整数
           

• 浮点类型

DECLARE
    score1 real;             --4字节
    score2 double precision; --8字节
           

• 序数类型

  smallserial、serial和bigserial类型不是真正的类型，它们只是为了创建唯一标识符列而存在的方便符号（类似其它一些数据库中支持的AUTO_INCREMENT属性）。 在目前的实现中：

CREATE TABLE tablename (
    colname SERIAL
);

等价于以下语句：

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
           

声明：

DECLARE
    data_id1 smallserial/serial2; --2字节序数
    data_id2 serial/serial4;      --4字节序数
    data_id3 bigserial/serial8;   --8字节序数
           

• 字符类型

DECLARE
	name1 character varying(n)/varchar(n);	--有限制的变长
	name2 character(n)/char(n);	            --定长，空格填充
	name3 text  ;                            --无限变长
           

• 布尔类型

DECLARE
	is_correct boolean; --状态为真或假
           

• 数组类型

DECLARE
    pay_by_quarter1 integer[] ;       --integer类型一维数组
    pay_by_quarter2 integer ARRAY[4]; --integer类型一维数组
    schedule        text[][];         --text类型二维数组
    
           

• JSON类型

  JSON类型包括两种类型，JSON和JSONB类型。它们几乎接受完全相同的值集合作为输入。

DECLARE
    config1 JSON;  --JSON类型
    config2 JSONB; --JSONB类型
           

高级数据类型

• 复制类型

  %TYPE

  提供了一个变量或表列的数据类型。你可以用它来声明将保持数据库值的变量。例如，如果你在users中有一个名为user_id的列。要定义一个与users.user_id具有相同数据类型的变量：

DECLARE
    user_id users.user_id%TYPE;
           

• 行类型

  一个组合类型的变量被称为一个行变量（或行类型变量）。这样一个变量可以保持一个SELECT或FOR查询结果的一整行，前提是查询的列集合匹配该变量被声明的类型。该行值的各个域可以使用通常的点号标记访问，例如rowvar.field。

DECLARE
    t2_row table2%ROWTYPE;--table2表的行类型
           

• 记录类型

  记录变量和行类型变量类似，但是它们没有预定义的结构。它们采用在一个SELECT或FOR命令期间为其赋值的行的真实行结构。一个记录变量的子结构能在每次它被赋值时改变。这样的结果是直到一个记录变量第一次被赋值之前，它都没有子结构，并且任何尝试访问其中一个域都会导致一个运行时错误。

DECLARE
    name RECORD; --记录类型
           

以上为本篇文章介绍的变量声明以及常用的数据类型，欢迎各位批评指正。

关于作者

相关链接

• 基于PostgreSQL和PostGIS的坐标转换函数，支持点、线、面的WGS84和CGCS2000与GCJ02和BD09坐标系与之间互转
• 统一社会信用代码相关的PostgreSQL函数，包括验证、随机生成等