一、视图
1.1创建/修改视图
CREATE OR REPLACE VIEW view_xsxx AS
SELECT a.sno,sname,sdept,cno,grade FROM test.student a,test.sc b where a.sno=b.sno;
1.2查询视图
SELECT * FROM view_xsxx;
1.3删除视图
DROP VIEW view_xsxx;
二、函数
2.1模板
CREATE OR REPLACE FUNCTION test.hs() RETURNS INTEGER AS
$js$
DECLARE js INTEGER ;
BEGIN
SELECT count(*) INTO js FROM test.student ;
RETURN js;
END;
$js$
LANGUAGE plpgsql; ---创建/修改
SELECT test.hs(); ----查询
DROP function test.hs(); ----删除
2.2示例
示例1.字符简单连接
CREATE OR REPLACE FUNCTION test.zifu(name varchar) returns varchar AS
$$
DECLARE
str1 VARCHAR;
str2 VARCHAR;
BEGIN
str1=name;
str2=$1 || 'to' || name;
RETURN str2;
END;
$$
LANGUAGE plpgsql;
SELECT test.zifu('ceshi'); -------结果:返回‘ceshitoceshi’
示例2.根据身份证号得出省市县、年龄