兼容INSERT ALL/FIRST子句语法
vastbase=# CREATE TABLE small_orders
vastbase-# (order_id NUMERIC NOT NULL,
vastbase(# customer_id NUMERIC NOT NULL,
vastbase(# order_total NUMERIC
vastbase(# );
CREATE TABLE
vastbase=# CREATE TABLE medium_orders AS SELECT * FROM small_orders;
CREATE temp TABLE large_orders AS SELECT * FROM small_orders;
CREATE TABLE special_orders
(order_id NUMERIC NOT NULL,
customer_id NUMERIC NOT NULL,
order_total NUMERIC,
other NUMERIC
);
vastbase=# CREATE TABLE orders
vastbase-# (order_id NUMERIC NOT NULL,
vastbase(# customer_id NUMERIC NOT NULL,
vastbase(# order_total NUMERIC ,
vastbase(# other NUMERIC
vastbase(# );
CREATE TABLE
vastbase=# ^M
vastbase=#
vastbase=# INSERT INTO orders SELECT 1, 1, 10000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 20000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 30000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 210000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 220000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 110000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 120000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 130000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 140000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 310000, 1 ;
INSERT 0 1
vastbase=# INSERT INTO orders SELECT 1, 1, 340000, 1 ;
INSERT 0 1
vastbase=# INSERT ALL
vastbase-# WHEN ottl < 100000 THEN
vastbase-# INTO small_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# WHEN ottl > 100000 and ottl < 200000 THEN
vastbase-# INTO medium_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# WHEN ottl > 200000 THEN
vastbase-# into large_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
vastbase-# o.other
vastbase-# FROM orders o;
INSERT 0 11
vastbase=# select * from small_orders;
order_id | customer_id | order_total
----------+-------------+-------------
1 | 10000 | 1
1 | 20000 | 1
1 | 30000 | 1
(3 rows)
vastbase=# select * from medium_orders;
order_id | customer_id | order_total
----------+-------------+-------------
1 | 110000 | 1
1 | 120000 | 1
1 | 130000 | 1
1 | 140000 | 1
(4 rows)
vastbase=# select * from large_orders;
order_id | customer_id | order_total
----------+-------------+-------------
1 | 210000 | 1
1 | 220000 | 1
1 | 310000 | 1
1 | 340000 | 1
(4 rows)
vastbase=# select * from special_orders;
order_id | customer_id | order_total | other
----------+-------------+-------------+-------
(0 rows)
vastbase=# delete from small_orders;
DELETE 3
vastbase=# delete from medium_orders;
DELETE 4
vastbase=# delete from large_orders;
DELETE 4
vastbase=# delete from special_orders;
DELETE 0
vastbase=#
vastbase=#
vastbase=# INSERT First
vastbase-# WHEN ottl = 1123000 THEN
vastbase-# INTO small_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# INTO small_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# WHEN ottl > 1 THEN
vastbase-# INTO medium_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# INTO medium_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# INTO medium_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# WHEN ottl > 200000 THEN
vastbase-# into large_orders
vastbase-# VALUES(oid, ottl, cid)
vastbase-# SELECT o.order_id oid, o.customer_id cid, o.order_total ottl,
vastbase-# o.other
vastbase-# FROM orders o;
INSERT 0 33
vastbase=# select count(*) from small_orders;
count
-------
0
(1 row)
vastbase=# select count(*) from medium_orders;
count
-------
33
(1 row)
vastbase=# select count(*) from large_orders;
count
-------
0
(1 row)
vastbase=# select count(*) from special_orders;
count
-------
0
(1 row)
