PostgreSQL学习笔记(一)

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, empno, salary, 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进行数据库表空间的查询；

[2]可以使用pg_user查询数据库用户；

 

Q6：PostgreSQL如何实现Oracle的Merge功能？

答：

 

 

 

Q7：PostgreSQL有哪些系统函数？它的打印输出函数是什么？

答：在pl/pgsql中，可以通过如下语句进行信息的输出。

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];