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