postgres高级总结

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

-- --------------------------------返回表中数据的函数----------------------------------------------------------------

select * from table_order_class;   
select user_id from orders where id = 3850;


CREATE 
	OR REPLACE FUNCTION get_user_id_from_order_id ( order_id INT ) RETURNS TABLE ( r_user_id INT ) AS $$ BEGIN
		RETURN QUERY 
		select CAST (user_id as INTEGER) from orders where id = order_id;
END; $$ LANGUAGE'plpgsql';

-- test
SELECT get_user_id_from_order_id(3850);    

-- -----------------------------------循环更新表中的数据_也可以返回数据_以轮训的----------------------------------------------   
DROP FUNCTION update_table_order_class(); 

CREATE 
	OR REPLACE FUNCTION update_table_order_class() RETURNS TABLE ( table_order_class_id INT) AS $$ DECLARE
	var_r record;
BEGIN
		FOR var_r IN ( SELECT id, order_id FROM table_order_class )
		LOOP
		  update table_order_class set user_id = (SELECT get_user_id_from_order_id(CAST(var_r.order_id as INTEGER))) where id = var_r.id;
			table_order_class_id := var_r.id;
	RETURN NEXT;
	
END LOOP;
END; $$ LANGUAGE'plpgsql';

-- execute
SELECT update_table_order_class();


-- test 
select * from table_order_class as toc join orders as o on toc.order_id = o.id 
where toc.user_id != o.user_id


-- ------------------------------------------------------------------------------------------------

1
2
3
4
5

CREATE TABLE books (
  id              SERIAL PRIMARY KEY,
  title           VARCHAR(100) NOT NULL,
  primary_author  VARCHAR(100) NULL
);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

create table test_c (
  id integer PRIMARY KEY,
  name character varying(128)
);  

-- 再手动创建序列
CREATE SEQUENCE test_c_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;

-- 将序列赋值给主键
alter table test_c alter column id set default nextval('test_c_id_seq');