一、安装
操作系统:windows7
安装介质:postgresql-9.1.3-1-windows.exe
二、psql控制台简单使用
1打开psql
2根据提示运行help
3列出表命令
三、安装uuid的扩展
postgresql9.1.3默认没有安装uuid相关函数,虽然在目录C:\Program Files (x86)\PostgreSQL\9.1\lib下有uuid-ossp.dll文件。
安装很简单,感觉C:\Program Files (x86)\PostgreSQL\9.1\share\extension\uuid-ossp--1.0.sql文件提示,在psql窗口下执行命令:
/* contrib/uuid-ossp/uuid-ossp--1.0.sql */ -- complain if script is sourced in psql, rather than via CREATE EXTENSION \echo Use "CREATE EXTENSION uuid-ossp" to load this file. \quit CREATE FUNCTION uuid_nil() RETURNS uuid AS ‘MODULE_PATHNAME‘, ‘uuid_nil‘ IMMUTABLE STRICT LANGUAGE C; CREATE FUNCTION uuid_ns_dns() .................
我这里因为是第二次执行,所以报错,这里没有管理,注意uuid-ossp需要加上字符串双引号
四、pgAdmin图形化工具简单使用
五、建表
以下名为dpi的schema是关联用户root(自己新建的用户,并且拥有superuser权限,因为要安装uuid扩展)
student 学生表
CREATE TABLE dpi.student
(
id uuid,
name character
varying(20),
age integer
)
course 课程表
CREATE TABLE dpi.course
(
id uuid,
name character
varying(30)
)
grade 分数表
CREATE TABLE dpi.grade
(
id uuid,
studentid
uuid,
classid uuid,
grade double precision
)
注:以上建表语句应该加上表的简单注释和字段意义注释!!
六、编写简单存储过程
postgresql中的存储过程就是函数。
1从postgresql文档中找到的实例1
CREATE OR REPLACE FUNCTION dpi.sales_tax(real)
RETURNS real
AS
$BODY$
DECLARE
subtotal ALIAS FOR $1;
BEGIN
RETURN subtotal * 0.06;
END;
$BODY$
LANGUAGE plpgsql
2从postgresql文档中找到的实例2
CREATE OR REPLACE FUNCTION dpi.add_three_values(IN v1 anyelement,
IN v2 anyelement, IN v3 anyelement, OUT sum anyelement)
RETURNS
anyelement AS
$BODY$
BEGIN
sum := v1 + v2 +
v3;
END;
$BODY$
LANGUAGE plpgsql
3添加学生的存储过程
CREATE OR REPLACE FUNCTION dpi.addstudent(character varying,
integer)
RETURNS void AS
$BODY$
insert into dpi.student
values(uuid_generate_v1(), $1, $2);
$BODY$
LANGUAGE sql
4添加课程的存储过程
CREATE OR REPLACE FUNCTION dpi.addcourse(character varying)
RETURNS void AS
$BODY$
insert into dpi.course
values(uuid_generate_v1(), $1);
$BODY$
LANGUAGE sql
5添加分数的存储过程
--copy自pgadmin工具:
CREATE OR REPLACE FUNCTION dpi.addgrade(character varying, double
precision, double precision, double precision)
RETURNS real
AS
$BODY$
declare
s_id uuid;
chinese_id
uuid;
english_id uuid;
math_id uuid;
begin
select id
into s_id from dpi.student where name = $1;
select id into
chinese_id from dpi.course where name = ‘语文‘;
select id into
english_id from dpi.course where name = ‘英语‘;
select id into
math_id from dpi.course where name = ‘数学‘;
insert into dpi.grade
values(uuid_generate_v1(), s_id, chinese_id, $2);
insert into
dpi.grade values(uuid_generate_v1(), s_id, english_id,
$3);
insert into dpi.grade values(uuid_generate_v1(), s_id,
math_id, $4);
raise notice ‘1‘;
return
1;
end;
$BODY$
LANGUAGE plpgsql
--原始写法
create or replace function dpi.addgrade(varchar, float, float,
float) returns real as $$
declare
s_id uuid;
chinese_id
uuid;
english_id uuid;
math_id uuid;
begin
select id
into s_id from dpi.student where name = $1;
select id into
chinese_id from dpi.course where name = ‘语文‘;
select id into
english_id from dpi.course where name = ‘英语‘;
select id into
math_id from dpi.course where name = ‘数学‘;
insert into dpi.grade
values(uuid_generate_v1(), s_id, chinese_id, $2);
insert into
dpi.grade values(uuid_generate_v1(), s_id, english_id,
$3);
insert into dpi.grade values(uuid_generate_v1(), s_id,
math_id, $4);
raise notice ‘1‘;
return 1;
end;
$$
language plpgsql;
6测试
注意:
存储过程的用法以及参数应该加注释
$$ language plpgsql和$$language sql是有区别的,$$ language plpgsql结尾的存储过程在使用$$ language sql结尾会出错。备注一下。