记录postgresql的学习

上面为表结构 
下面则为 相关函数写法  写这些主要是为了学习下与Oracle的区别

SELECT * FROM STUDENT CROSS JOIN TEACHER;--交叉连接
SELECT * FROM STUDENT S INNER JOIN TEACHER T ON S.TEACHER_ID=T.ID;--内连接
SELECT * FROM STUDENT S LEFT OUTER JOIN TEACHER T ON S.TEACHER_ID=T.ID;--左外连接
SELECT * FROM STUDENT S RIGHT OUTER JOIN TEACHER T ON S.TEACHER_ID=T.ID;--右外连接
SELECT * FROM STUDENT S FULL OUTER JOIN TEACHER T ON S.TEACHER_ID=T.ID;--全外连接

--视图
CREATE VIEW tea_stu_view AS
 SELECT s.id,s.name,t.tea_name FROM STUDENT S INNER JOIN TEACHER T ON S.TEACHER_ID=T.ID;
--视图调用
SELECT * FROM tea_stu_view;

--存储过程
CREATE OR REPLACE FUNCTION this_is_function(table_name text,column_name text) RETURNS INTEGER AS $maxId$
 declare
   mysql text;
   myID integer;
 begin
   mysql:='select max('|| quote_ident(column_name) || ')from '|| quote_ident(table_name);
   execute mysql into myID;
   if myID is null or myID=0 then return 1;
    else return myID+1;
      end if;
 end;
$maxId$ language plpgsql;
--执行存储过程
select this_is_function('teacher','id')；

--触发器调用函数
CREATE OR REPLACE FUNCTION trigger_fun() RETURNS TRIGGER as $trigger_fun$
  declare
    myID integer;
    mysql text;
  begin
    mysql:='select max(id)+1 from teacher ';
    execute mysql into myID;
    if tg_op='INSERT' then
       INSERT INTO public.teacher(id,tea_name) values (myID,'红老师');
    end if;
    return null;
  end;
$trigger_fun$ language plpgsql;
--创建触发器
CREATE TRIGGER trigger_fun AFTER INSERT ON student for each row execute procedure trigger_fun();
--触发器触动方式
insert into public.student(id,name,sex,teacher_id) values (6,'小三','男','3');

--创建序列
create sequence test_seq increment by 1 minvalue 1 no maxvalue start with 1;
select nextval('test_seq'); --序列调用

--建索引
CREATE INDEX student_index  ON student(teacher_id);