postgresql存储过程_单行单列

前言

postgresql是没有像其他数据库一样有存储过程的，在这其实就是函数，由一组 sql 语句组成，实现比较复杂的数据库操作；

语法解析

CREATE [OR REPLACE] FUNCTION function_name (arguments)   
RETURNS return_datatype AS $variable_name$  
  DECLARE  
    declaration;  
    [...]  
  BEGIN  
    < function_body >  
    [...]  
    RETURN { variable_name | value }  
  END; LANGUAGE plpgsql;

准备工作

CREATE TABLE "department" (
  "id" int4 NOT NULL,
  "d_code" varchar(50) ,
  "d_name" varchar(50) NOT NULL,
  "d_parentid" int4 NOT NULL DEFAULT 0
);

INSERT INTO "department" VALUES (2, '2', '2', 1);
INSERT INTO "department" VALUES (3, '3', '3', 1);
INSERT INTO "department" VALUES (4, '4', '4', 2);
INSERT INTO "department" VALUES (1, 'bb', '1', 0);

ALTER TABLE "department" ADD CONSTRAINT "department_pkey" PRIMARY KEY ("id");

建完再复制一个department1表

实验

• create or replace ：创建或替代方法
• returns：声明返回值类型
• declare：声明变量类型
• begin——end：方法体
• language：声明存储过程的实现语言

---

• 基础版

-- 创建函数
create or replace function agetcount()
returns integer as $$
declare
 counts integer;
begin
	select count(*) into counts from department;
	return counts;
end;
$$ language plpgsql;

-- 调用测试
select agetcount();

---

• 将sql赋值给变量

create or replace function agetcount1()
returns integer as $$
declare
 mysql text;
 counts integer;
begin
	mysql:='select count(*) from department';
	execute mysql into counts;
	return counts;
end;
$$ language plpgsql;

select agetcount1();

---

• 带变量，需要拼接字符串1

create or replace function agetcount2(tableName text, columnName text)
returns text as $$
declare
 mysql text;
begin
mysql:='select count('
    || quote_ident(columnName) 
    || ') from '
    || quote_ident(tableName);
return mysql;
end;
$$ language plpgsql;
select agetcount2('department', 'id');

结果：
select count(id) from department

---

• 带变量，需要拼接字符串2

create or replace function agetcount3(tableName text, columnName text)
returns integer as $$
declare
 mysql text;
 counts integer;
begin
	mysql:='select count("' || $2 || '") from "' || $1 || '" ';
	execute mysql into counts using tableName, columnName;
	if counts > 3 then
    	return counts;
	else return 1;
	end if;
	end;
$$ language plpgsql;

select agetcount3('department', 'id');

注意

1. 函数的参数必须声明类型
2. || 表示字符串拼接符号
3. 存储过程中的对象不能直接引用变量，要用 quote_ident，它的作用是为字符串加上 双引号
4. 在 sql 语句中，大写，全部会变成小写，如果想保留大写，需要加 双引号
5. 用 using 调取变量，此时需要自己加 双引号 以保留 大写
6. 对应的是函数的参数位置，跟 using 后的顺序无关
7. if 后面有个 then
8. text 可变长度字符串
9. 每句末尾必须带分号