This is an automated email from the ASF dual-hosted git repository.
kxiao pushed a commit to branch branch-2.0
in repository https://gitbox.apache.org/repos/asf/doris.git
commit 458b48e22e6407f0cc1e27d951026a1fb7bb9ecb
Author: zy-kkk <zhongy...@gmail.com>
AuthorDate: Sun Sep 3 10:52:01 2023 +0800
[enhancement](jdbc catalog ) support postgresql partition table (#23744)
---
.../postgresql/init/02-create-table.sql | 52 ++++++++++++++++++++++
.../docker-compose/postgresql/init/04-insert.sql | 14 ++++++
.../jdbc/client/JdbcPostgreSQLClient.java | 2 +-
.../jdbc/test_pg_jdbc_catalog.out | 40 +++++++++++++++++
.../jdbc/test_pg_jdbc_catalog.groovy | 10 +++++
5 files changed, 117 insertions(+), 1 deletion(-)
diff --git
a/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
b/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
index d89acfb6b2..92952cdd22 100644
--- a/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
+++ b/docker/thirdparties/docker-compose/postgresql/init/02-create-table.sql
@@ -178,3 +178,55 @@ CREATE TABLE catalog_pg_test.jsonb_test (
type varchar(10),
value jsonb
);
+
+CREATE TABLE catalog_pg_test.person_r (
+ age int not null,
+ city varchar not null
+)
+ PARTITION BY RANGE (age);
+
+
+create table catalog_pg_test.person_r1 partition of catalog_pg_test.person_r
for values from (MINVALUE) to (10);
+create table catalog_pg_test.person_r2 partition of catalog_pg_test.person_r
for values from (11) to (20);
+create table catalog_pg_test.person_r3 partition of catalog_pg_test.person_r
for values from (21) to (30);
+create table catalog_pg_test.person_r4 partition of catalog_pg_test.person_r
for values from (31) to (MAXVALUE);
+
+CREATE TABLE catalog_pg_test.tb_test_alarm (
+ id varchar(64) NOT NULL,
+ alarm_type varchar(10) NOT NULL,
+ happen_time timestamptz NOT NULL,
+ CONSTRAINT tb_test_pk PRIMARY KEY (id)
+);
+
+create table catalog_pg_test.tb_test_alarm_2020_12 () inherits
(catalog_pg_test.tb_test_alarm);
+create table catalog_pg_test.tb_test_alarm_2020_11 () inherits
(catalog_pg_test.tb_test_alarm);
+create table catalog_pg_test.tb_test_alarm_2020_10 () inherits
(catalog_pg_test.tb_test_alarm);
+create table catalog_pg_test.tb_test_alarm_2020_09 () inherits
(catalog_pg_test.tb_test_alarm);
+
+
+--创建分区函数
+CREATE OR REPLACE FUNCTION alarm_partition_trigger()
+RETURNS TRIGGER AS $$
+BEGIN
+ IF NEW.happen_time >= '2020-09-01 00:00:00' and NEW.happen_time <=
'2020-09-30 23:59:59'
+ THEN
+ INSERT INTO catalog_pg_test.tb_test_alarm_2020_09 VALUES (NEW.*);
+ ELSIF NEW.happen_time >= '2020-10-01 00:00:00' and NEW.happen_time <=
'2020-10-31 23:59:59'
+ THEN
+ INSERT INTO catalog_pg_test.tb_test_alarm_2020_10 VALUES (NEW.*);
+ ELSIF NEW.happen_time >= '2020-11-01 00:00:00' and NEW.happen_time <=
'2020-11-30 23:59:59'
+ THEN
+ INSERT INTO catalog_pg_test.tb_test_alarm_2020_11 VALUES (NEW.*);
+ ELSIF NEW.happen_time >= '2020-12-01 00:00:00' and NEW.happen_time <=
'2020-12-31 23:59:59'
+ THEN
+ INSERT INTO catalog_pg_test.tb_test_alarm_2020_12 VALUES (NEW.*);
+END IF;
+RETURN NULL;
+END;
+$$
+LANGUAGE plpgsql;
+
+--挂载分区Trigger
+CREATE TRIGGER insert_almart_partition_trigger
+ BEFORE INSERT ON catalog_pg_test.tb_test_alarm
+ FOR EACH ROW EXECUTE PROCEDURE alarm_partition_trigger();
\ No newline at end of file
diff --git a/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
b/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
index eab36c4edd..245e70b2b2 100644
--- a/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
+++ b/docker/thirdparties/docker-compose/postgresql/init/04-insert.sql
@@ -2704,3 +2704,17 @@ INSERT INTO catalog_pg_test.jsonb_test (type,value)
VALUES
'jsonb2',
NULL
);
+
+insert into catalog_pg_test.person_r(age, city) VALUES (1, 'GZ');
+insert into catalog_pg_test.person_r(age, city) VALUES (2, 'SZ');
+insert into catalog_pg_test.person_r(age, city) VALUES (21, 'SZ');
+insert into catalog_pg_test.person_r(age, city) VALUES (13, 'BJ');
+insert into catalog_pg_test.person_r(age, city) VALUES (43, 'SH');
+insert into catalog_pg_test.person_r(age, city) VALUES (28, 'HK');
+
+insert into catalog_pg_test.tb_test_alarm
+values('1','A','2020-09-02 00:00:00')
+ ,('2','A','2020-10-02 00:00:00')
+ ,('3','A','2020-11-02 00:00:00')
+ ,('4','A','2020-12-02 00:00:00')
+;
diff --git
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
index 9b0bd05d22..7fe1803ad5 100644
---
a/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
+++
b/fe/fe-core/src/main/java/org/apache/doris/datasource/jdbc/client/JdbcPostgreSQLClient.java
@@ -35,7 +35,7 @@ public class JdbcPostgreSQLClient extends JdbcClient {
@Override
protected String[] getTableTypes() {
- return new String[] {"TABLE", "VIEW", "MATERIALIZED VIEW", "FOREIGN
TABLE"};
+ return new String[] {"TABLE", "PARTITIONED TABLE", "VIEW",
"MATERIALIZED VIEW", "FOREIGN TABLE"};
}
@Override
diff --git
a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
index 0ab62f0d97..1a2cf62abf 100644
--- a/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
+++ b/regression-test/data/external_table_p0/jdbc/test_pg_jdbc_catalog.out
@@ -2161,6 +2161,46 @@ true abc def 2022-10-11 1.234 1
2 99 2022-10-22T10:59:59 34.123
-- !filter3 --
1 false 12.123456 10.16.10.14/32 10.16.10.14
ff:ff:ff:ff:ff:aa 1010101010 01010 1 {"id":1}
+-- !partition_1_0 --
+1 GZ
+13 BJ
+2 SZ
+21 SZ
+28 HK
+43 SH
+
+-- !partition_1_1 --
+1 GZ
+2 SZ
+
+-- !partition_1_2 --
+13 BJ
+
+-- !partition_1_3 --
+21 SZ
+28 HK
+
+-- !partition_1_4 --
+43 SH
+
+-- !partition_2_0 --
+1 A 2020-09-02T08:00
+2 A 2020-10-02T08:00
+3 A 2020-11-02T08:00
+4 A 2020-12-02T08:00
+
+-- !partition_2_1 --
+1 A 2020-09-02T08:00
+
+-- !partition_2_2 --
+2 A 2020-10-02T08:00
+
+-- !partition_2_3 --
+3 A 2020-11-02T08:00
+
+-- !partition_2_4 --
+4 A 2020-12-02T08:00
+
-- !test_insert1 --
doris1 18
diff --git
a/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
b/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
index 9d74c70c8e..db7374d929 100644
--- a/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
+++ b/regression-test/suites/external_table_p0/jdbc/test_pg_jdbc_catalog.groovy
@@ -76,6 +76,16 @@ suite("test_pg_jdbc_catalog", "p0") {
order_qt_filter1 """ select * from test10 where 1 = 1 order by id;
"""
order_qt_filter2 """ select * from test10 where id = 1 order by id;
"""
order_qt_filter3 """ select * from test10 where 1 = 1 and id = 1
order by id; """
+ order_qt_partition_1_0 "select * from person_r;"
+ order_qt_partition_1_1 "select * from person_r1;"
+ order_qt_partition_1_2 "select * from person_r2;"
+ order_qt_partition_1_3 "select * from person_r3;"
+ order_qt_partition_1_4 "select * from person_r4;"
+ order_qt_partition_2_0 "select * from tb_test_alarm;"
+ order_qt_partition_2_1 "select * from tb_test_alarm_2020_09;"
+ order_qt_partition_2_2 "select * from tb_test_alarm_2020_10;"
+ order_qt_partition_2_3 "select * from tb_test_alarm_2020_11;"
+ order_qt_partition_2_4 "select * from tb_test_alarm_2020_12;"
// test insert
String uuid1 = UUID.randomUUID().toString();
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org
For additional commands, e-mail: commits-h...@doris.apache.org
