Postgresql学习笔记
Q1:Oracle对应的并交差操作，递归查询操作，WITH语法，窗口分析函数在Postgresql对应怎么表达？
答：[1] rs1 union [all] rs2
Rs1 intersect[all] rs2
Rs1 except [all] rs2
实现对两个集合的并交差语法
[2] 如果知道父级记录，想递归查询出子级记录，可以通过如下语句：
withrecursive r as (
select * from rs where id = rootid
union all
select * from rs,r where rs.parent_id =r.id
)
Select * from r order by id;
如果知道子级记录，想递归查询出父级记录，可以通过如下语句：
with recursive r as (
select * from rs where id = leafid
union all
select * from rs,r where r.parent_id =rs.id
)
Select * from r order by id;
[3]with查询：
WITH regional_sales AS (
SELECT region,SUM(amount) AS total_sales
FROM orders
GROUP BY region
),top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECTSUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region,product;
[4]窗口分析函数：
[4.1] SELECTdepname,empno,salary,avg(salary) OVER (PARTITION BY depname) FROM empsalary;
[4.2] SELECTdepname,rank() OVER (PARTITION BY depname ORDER BY salary DESC)FROM empsalary;
[4.3] SELECTsalary,sum(salary) OVER () FROM empsalary;
[4.4] SELECTsum(salary) OVER w,avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Q2：Oracle包体功能在Postgresql中如何实现？
答：Postgresql中好像没有类似ORACLE包的功能，它只有函数功能，不过，PG只提供了命名空间用以对函数进行归类。
CREATE OR REPLACE FUNCTION somefunc() RETURNS integer AS $$
<< outerblock>>
DECLARE
quantity integer := 30;
BEGIN
RAISE NOTICE ’Quantity here is %’,quantity; -- Prints 30
quantity := 50;
--
-- Create a subblock
--
DECLARE
quantity integer := 80;
BEGIN
RAISE NOTICE ’Quantity here is %’,quantity; -- Prints 80
RAISE NOTICE ’Outer quantity here is %’,outerblock.quantity; -- Prints 50
END;
RAISE NOTICE ’Quantity here is %’,quantity; -- Prints 50
RETURN quantity;
END;
$$ LANGUAGE plpgsql;
Perform somefunc();
--异常处理
BEGIN;
NULL;
EXCEPTION
WHENNO_DATA_FOUND THEN
RAISE EXCEPTION ‘employee % not found’,myname;
WHENTOO_MANY_ROWS THEN
RAISE EXCEPTION ‘employee % not unique’;
END;
Q3：Postgresql如何创建分区表？如何创建分区索引？分区表如何使用？
答：[1]Postgresql中分区通过继承的方式来实现，父表只提供定义，分区数据存储在各子表中，比如如下案例：
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
);
CREATE TABLE measurement_y2006m02 (
CHECK ( logdate >= DATE ’2006-02-01’AND logdate< DATE ’2006-03-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
CHECK ( logdate >= DATE ’2006-03-01’AND logdate< DATE ’2006-04-01’ )
) INHERITS (measurement);
...
CREATE TABLE measurement_y2007m11 (
CHECK ( logdate >= DATE ’2007-11-01’AND logdate< DATE ’2007-12-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
CHECK ( logdate >= DATE ’2007-12-01’AND logdate< DATE ’2008-01-01’ )
) INHERITS (measurement);
CREATE TABLE measurement_y2008m01 (
CHECK ( logdate >= DATE ’2008-01-01’AND logdate< DATE ’2008-02-01’ )
) INHERITS (measurement);
--分区索引的创建
CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02(logdate);
CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate);
...
CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate);
CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate);
CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);
[2]插入的时候可以通过建立规则以及触发器的方式来实现
--01规则
CREATE RULE measurement_insert_y2006m02 AS
ON INSERT TO measurement WHERE
( logdate >= DATE ’2006-02-01’ AND logdate<DATE ’2006-03-01’ )
DO INSTEAD
INSERT INTO measurement_y2006m02 VALUES (NEW. *);
...
CREATE RULE measurement_insert_y2008m01 AS
ON INSERT TO measurement WHERE
( logdate >= DATE ’2008-01-01’ AND logdate<DATE ’2008-02-01’ )
DO INSTEAD
INSERT INTO measurement_y2008m01 VALUES (NEW. *);
--02触发器的方式
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO measurement_y2008m01 VALUES (NEW. *);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER insert_measurement_trigger
BEFORE INSERT ON measurement
FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
[3]分区表的管理
--删除表分区
DROP TABLE measurement_y2006m02;
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
--创建新分区
CREATE TABLE measurement_y2008m02 (
CHECK ( logdate >= DATE ’2008-02-01’AND logdate< DATE ’2008-03-01’ )
) INHERITS (measurement);
--非分区表变成分区表
CREATE TABLE measurement_y2008m02
(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02
CHECK ( logdate >= DATE ’2008-02-01’AND logdate< DATE ’2008-03-01’ );
\copy measurement_y2008m02 from ’measurement_y2008m02’
-- possibly some other data preparation work
ALTER TABLE measurement_y2008m02 INHERIT measurement;
[4]分区表的访问
SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE ’2008-01-01’;
Q4：Postgresql如何查询系统正在运行的会话对应的执行sql？以及对应的执行计划？Postgresql的优化器工作原理？
答：[1]查询某个sql语句的执行计划，可通过如下命令来实现：
EXPLAIN SELECT * FROM tenk1;
Q5：Postgresql如何查询系统表空间，用户等信息？
答：[1]可以使用pg_tablespace进行数据库表空间的查询；
Q6：Postgresql如何实现Oracle的Merge功能？
答：
Q7：Postgresql有哪些系统函数？它的打印输出函数是什么？
RAISE NOTICE ‘HELLO WORD’;
Q8：Postgresql如何实现动态sql？
答：示例如下：
EXEC sql BEGIN DECLARE SECTION;
const char *stmt = "SELECT a,b,c FROM test1 WHERE a > ?";
int v1,v2;
VARCHAR v3;
EXEC sql END DECLARE SECTION;
EXEC sql PREPARE mystmt FROM :stmt;
...
EXEC sql EXECUTE mystmt INTO v1,v2,v3 USING 37;
Q9: Postgresql中user,tablespace,schema,dbinstance之间的关系,linux中Postgresql创建表空间如何挂载到linux下的文件系统上或者裸设备中？
答：
Q10: Postgresql中权限如何控制？为什么用pgadmin进去的时候，任何一个schema下的对象都可以查询？默认登录用户名是什么以及此用户的权限级别是什么？
答：
Q11:Postgresql中有哪些控制结构？
答：
[1]IF-ELSE
IF ... THEN
• IF ... THEN ... ELSE
• IF ... THEN ... ELSIF ... THEN ... ELSE
[2] IF parentid IS NULL OR parentid = ”
THEN
RETURN fullname;
ELSE
RETURN hp_true_filename(parentid) || ’/’ || fullname;
END IF;
IF v_count > 0 THEN
INSERT INTO users_count (count) VALUES (v_count);
RETURN ’t’;
ELSE
RETURN ’f’;
END IF;
[2]CASE语法
CASE x
WHEN 1,2 THEN
msg := ’one or two’;
ELSE
msg := ’other value than one or two’;
END CASE;
[3]LOOP语法
LOOP
-- some computations
EXIT WHEN count > 100;
CONTINUE WHEN count < 50;
-- some computations for count IN [50 .. 100]
END LOOP;
[4]WHILE语法
WHILE amount_owed > 0 AND gift_certificate_balance> 0 LOOP
-- some computations here
END LOOP;
[5]FOR语法
FOR targetINEXECUTE text_expression[ USING expression[,... ] ] LOOP
statements
END LOOP [ label];
