CN104731863A - Method for simplifying PostgreSQL division codes - Google Patents
Method for simplifying PostgreSQL division codes Download PDFInfo
- Publication number
- CN104731863A CN104731863A CN201510078225.3A CN201510078225A CN104731863A CN 104731863 A CN104731863 A CN 104731863A CN 201510078225 A CN201510078225 A CN 201510078225A CN 104731863 A CN104731863 A CN 104731863A
- Authority
- CN
- China
- Prior art keywords
- sub
- name
- trigger
- trigger function
- postgresql
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Granted
Links
Classifications
-
- G—PHYSICS
- G06—COMPUTING OR CALCULATING; COUNTING
- G06F—ELECTRIC DIGITAL DATA PROCESSING
- G06F16/00—Information retrieval; Database structures therefor; File system structures therefor
- G06F16/20—Information retrieval; Database structures therefor; File system structures therefor of structured data, e.g. relational data
- G06F16/21—Design, administration or maintenance of databases
Landscapes
- Engineering & Computer Science (AREA)
- Databases & Information Systems (AREA)
- Theoretical Computer Science (AREA)
- Data Mining & Analysis (AREA)
- Physics & Mathematics (AREA)
- General Engineering & Computer Science (AREA)
- General Physics & Mathematics (AREA)
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention relates to a method for simplifying PostgreSQL division codes. The method resolves the problems that as each main table needs to correspond to at least one trigger function, more main tables are, more trigger functions need writing, the maintenance workloads of the codes are larger and the error probability is higher. According to the technical scheme, the method includes the following steps of firstly, building the main tables, secondly, building secondary tables, thirdly, building trigger functions, and fourthly, building a before trigger, transmitting four character string parameters, and simplifying the PostgreSQL division codes. In the fourth step of the building the before trigger, the transmitted four character string parameters are schemaname, tablename, partition name and partition method respectively. In the first step, the trigger functions receive the four character string parameters of schemaname, tablename, partition name and partition method.
Description
Technical field
The present invention is a kind of PostgreSQL partition method, particularly relates to a kind of method simplifying PostgreSQL division code.
Background technology
The partition data route of PostgreSQL database is realized by trigger, show partition table every month for such as one, use master meter and sublist to the transparent way of application, master meter creates trigger function, user accesses master meter, by the data that user transmits, trigger function judges data are inside which sublist.
The shortcoming of prior art: under normal circumstances, each master meter needs correspondence at least 1 trigger function, so master meter is more, need to write more trigger functions, the maintenance of code is larger, and the probability of makeing mistakes is also higher.Usual trigger function uses static code, such as, so once newly-increased sublist, just need to revise trigger, present sublist has been created in Dec, 2014, when newly-increased sublist quantity is in Dec, 2015, just needs to revise code of trigger.If the non-time update of code of trigger, will occur that data cannot be inserted into the situation of correct sublist by 2015.
Summary of the invention
The object of the invention is to there is each master meter need correspondence at least 1 trigger function for solving current technical scheme, so master meter is more, need to write more trigger functions, the maintenance of code is larger, the problem that the probability of makeing mistakes is also higher, provides a kind of method simplifying PostgreSQL division code.
The technical solution adopted for the present invention to solve the technical problems is: a kind of method simplifying PostgreSQL division code, comprises the following steps:
Step one, creates master meter,
Step 2, creates sublist,
Step 3, creates trigger function,
Step 4, creates before trigger, imports four string arguments into, simplifies PostgreSQL division code,
The performing step of described trigger function comprises following sub-step:
Sub-step one, trigger function receives four string arguments,
Sub-step two, trigger function opens a vernier, arranges name normally, the data type that row are corresponding from unit table pg_catalog.pg_attribute according to the relid traversal of trigger,
Sub-step three, trigger function performs encapsulation row name, and the form of each encapsulation row name is column_name,
Sub-step four, trigger function performs encapsulation value, and the form of each encapsulation value is NEW|OLD.TABLE_NAME.COLUMN_NAME::TYPE,
Sub-step five, trigger function judges whether row name equals the subregion field name imported into, if so, then obtains sublist name according to the value of the partitioning algorithm imported into and this field, trigger function judges that row name is not equal to the subregion field name imported into and then continues to perform sub-step six
Sub-step six, if vernier also has data, then jumps to sub-step two continuation, otherwise continues to perform sub-step seven,
Sub-step seven, by the value that sub-step three and sub-step four and sub-step six obtain, be merged into DMLSQL, sub-step eight, call the dynamic sql of sub-step seven, perform this dynamic sql by correct sublist, sub-step nine, returns sky, exits trigger function.
As preferably, in described step 4, create before trigger, import four string arguments into, described four string arguments are respectively: schemaname, tablename, subregion row name and partition method.
As preferably, in described sub-step one, trigger function receives schemaname, tablename, subregion row name and partition method four string arguments.
The invention solves the problem that trigger function code is safeguarded, newly-increased sublist no longer needs to make any amendment to trigger function, solve the uncurrent problem of trigger function, newly-increased partition table does not need to increase trigger function again newly, a function settles all partitioning algorithms, solve the problem that different partitioning algorithm needs to write multiple trigger function, such as monthly subregion and per year subregion need to write 2 trigger functions, use method of the present invention only can write a trigger function.The present invention utilizes dynamic code to replace original static code in trigger function, make during newly-increased sublist, not need amendment trigger function, PostgreSQL unit table and trigger character string interface is utilized in trigger function, automatic packaging parent table information, make multiple master meter can share a trigger function. accomplish 1 function, recycling, the present invention is by trigger character string interface, and import subregion field into, divisional type, such as create_time field, monthly subregion, or subregion per year, or subregion etc. on time. according to the content imported into, encapsulation dynamic sql, data are introduced and specifies sublist.
Substantial effect of the present invention is: during newly-increased sublist, does not need amendment trigger function.During newly-increased master meter, do not need newly-increased trigger function.When using different partitioning algorithms, do not need newly-increased trigger function.
Accompanying drawing explanation
Fig. 1 is the trigger function logics figure in the present invention.
Embodiment
Below by specific embodiment, and by reference to the accompanying drawings, technical scheme of the present invention is described in further detail.
Embodiment:
Simplify a method for PostgreSQL division code, comprise the following steps:
Step one, creates master meter,
Step 2, creates sublist,
Step 3, creates trigger function,
Step 4, creates before trigger, imports four string arguments into, simplifies PostgreSQL division code,
The performing step of described trigger function comprises following sub-step:
Sub-step one, trigger function receives four string arguments,
Sub-step two, trigger function opens a vernier, arranges name normally, the data type that row are corresponding from unit table pg_catalog.pg_attribute according to the relid traversal of trigger,
Sub-step three, trigger function performs encapsulation row name, and the form of each encapsulation row name is column_name,
Sub-step four, trigger function performs encapsulation value, and the form of each encapsulation value is NEW|OLD.TABLE_NAME.COLUMN_NAME::TYPE,
Sub-step five, trigger function judges whether row name equals the subregion field name imported into, if so, then obtains sublist name according to the value of the partitioning algorithm imported into and this field, trigger function judges that row name is not equal to the subregion field name imported into and then continues to perform sub-step six
Sub-step six, if vernier also has data, then jumps to sub-step two continuation, otherwise continues to perform sub-step seven,
Sub-step seven, by the value that sub-step three and sub-step four and sub-step six obtain, be merged into DMLSQL, sub-step eight, call the dynamic sql of sub-step seven, perform this dynamic sql by correct sublist, sub-step nine, returns sky, exits trigger function.
In described step 4, create before trigger, import four string arguments into, described four string arguments are respectively: schemaname, tablename, subregion row name and partition method.
In described sub-step one, trigger function receives schemaname, tablename, subregion row name and partition method four string arguments.
The invention solves the problem that trigger function code is safeguarded, newly-increased sublist no longer needs to make any amendment to trigger function, solve the uncurrent problem of trigger function, newly-increased partition table does not need to increase trigger function again newly, a function settles all partitioning algorithms, solve the problem that different partitioning algorithm needs to write multiple trigger function, such as monthly subregion and per year subregion need to write 2 trigger functions, use method of the present invention only can write a trigger function.The present invention utilizes dynamic code to replace original static code in trigger function, make during newly-increased sublist, not need amendment trigger function, PostgreSQL unit table and trigger character string interface is utilized in trigger function, automatic packaging parent table information, make multiple master meter can share a trigger function. accomplish 1 function, recycling, the present invention is by trigger character string interface, and import subregion field into, divisional type, such as create_time field, monthly subregion, or subregion per year, or subregion etc. on time. according to the content imported into, encapsulation dynamic sql, data are introduced and specifies sublist.
Above-described embodiment is one of the present invention preferably scheme, not does any pro forma restriction to the present invention, also has other variant and remodeling under the prerequisite not exceeding the technical scheme described in claim.
Claims (3)
1. simplify a method for PostgreSQL division code, it is characterized in that: comprise the following steps:
Step one, creates master meter,
Step 2, creates sublist,
Step 3, creates trigger function,
Step 4, creates before trigger, imports four string arguments into, simplifies PostgreSQL division code,
The performing step of described trigger function comprises following sub-step:
Sub-step one, trigger function receives four string arguments,
Sub-step two, trigger function opens a vernier, arranges name normally, the data type that row are corresponding from unit table pg_catalog.pg_attribute according to the relid traversal of trigger,
Sub-step three, trigger function performs encapsulation row name, and the form of each encapsulation row name is column_name,
Sub-step four, trigger function performs encapsulation value, and the form of each encapsulation value is NEW|OLD.TABLE_NAME.COLUMN_NAME::TYPE,
Sub-step five, trigger function judges whether row name equals the subregion field name imported into, if so, then obtains sublist name according to the value of the partitioning algorithm imported into and this field, trigger function judges that row name is not equal to the subregion field name imported into and then continues to perform sub-step six
Sub-step six, if vernier also has data, then jumps to sub-step two continuation, otherwise continues to perform sub-step seven,
Sub-step seven, by the value that sub-step three and sub-step four and sub-step six obtain, be merged into DMLSQL, sub-step eight, call the dynamic sql of sub-step seven, perform this dynamic sql by correct sublist, sub-step nine, returns sky, exits trigger function.
2. the method for simplification PostgreSQL division code according to claim 1, it is characterized in that: in described step 4, create before trigger, import four string arguments into, described four string arguments are respectively: schemaname, tablename, subregion row name and partition method.
3. the method for simplification PostgreSQL division code according to claim 2, it is characterized in that: in described sub-step one, trigger function receives schemaname, tablename, subregion row name and partition method four string arguments.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510078225.3A CN104731863B (en) | 2015-02-13 | 2015-02-13 | Simplify the method for PostgreSQL division codes |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN201510078225.3A CN104731863B (en) | 2015-02-13 | 2015-02-13 | Simplify the method for PostgreSQL division codes |
Publications (2)
| Publication Number | Publication Date |
|---|---|
| CN104731863A true CN104731863A (en) | 2015-06-24 |
| CN104731863B CN104731863B (en) | 2017-10-13 |
Family
ID=53455750
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN201510078225.3A Active CN104731863B (en) | 2015-02-13 | 2015-02-13 | Simplify the method for PostgreSQL division codes |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN104731863B (en) |
Cited By (1)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105573678A (en) * | 2015-12-17 | 2016-05-11 | 深圳市华讯方舟软件技术有限公司 | PostgreSQL block |
Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1825307A (en) * | 2005-10-31 | 2006-08-30 | 北京神舟航天软件技术有限公司 | SQL load mining-based automatic design method for physical database |
| US20110302151A1 (en) * | 2010-06-04 | 2011-12-08 | Yale University | Query Execution Systems and Methods |
| CN103020285A (en) * | 2012-12-27 | 2013-04-03 | 北京仿真中心 | Cross-database automatic coding method supporting multifield combination and system |
| CN103136263A (en) * | 2011-11-23 | 2013-06-05 | 英业达股份有限公司 | Method of automatically generating SQL statement |
| US20140108861A1 (en) * | 2012-10-15 | 2014-04-17 | Hadapt, Inc. | Systems and methods for fault tolerant, adaptive execution of arbitrary queries at low latency |
| US8886631B2 (en) * | 2010-06-04 | 2014-11-11 | Yale University | Query execution systems and methods |
-
2015
- 2015-02-13 CN CN201510078225.3A patent/CN104731863B/en active Active
Patent Citations (6)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN1825307A (en) * | 2005-10-31 | 2006-08-30 | 北京神舟航天软件技术有限公司 | SQL load mining-based automatic design method for physical database |
| US20110302151A1 (en) * | 2010-06-04 | 2011-12-08 | Yale University | Query Execution Systems and Methods |
| US8886631B2 (en) * | 2010-06-04 | 2014-11-11 | Yale University | Query execution systems and methods |
| CN103136263A (en) * | 2011-11-23 | 2013-06-05 | 英业达股份有限公司 | Method of automatically generating SQL statement |
| US20140108861A1 (en) * | 2012-10-15 | 2014-04-17 | Hadapt, Inc. | Systems and methods for fault tolerant, adaptive execution of arbitrary queries at low latency |
| CN103020285A (en) * | 2012-12-27 | 2013-04-03 | 北京仿真中心 | Cross-database automatic coding method supporting multifield combination and system |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN105573678A (en) * | 2015-12-17 | 2016-05-11 | 深圳市华讯方舟软件技术有限公司 | PostgreSQL block |
| CN105573678B (en) * | 2015-12-17 | 2018-11-09 | 深圳市华讯方舟软件技术有限公司 | A kind of PostgreSQL blocks |
Also Published As
| Publication number | Publication date |
|---|---|
| CN104731863B (en) | 2017-10-13 |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| CN107918666B (en) | Data synchronization method and system on block chain | |
| US10628449B2 (en) | Method and apparatus for processing database data in distributed database system | |
| US20190018879A1 (en) | Eigenvalue-based data query | |
| US20160314394A1 (en) | Method and device for constructing event knowledge base | |
| CN105487864A (en) | Method and device for automatically generating code | |
| CN112948427B (en) | Data query method, device, equipment and storage medium | |
| CN104021123A (en) | Method and system for data transfer | |
| US10289723B1 (en) | Distributed union all queries | |
| CN106354490A (en) | Dynamic layout method for EPG page | |
| CN102981882B (en) | Analytic method and device | |
| CN111475511A (en) | Data storage method, data access method, data storage device, data access device and data access equipment based on tree structure | |
| CN110019177A (en) | The method and apparatus of rule storage | |
| CN106970918B (en) | Method and device for generating unique identifier of international address | |
| CN111901453B (en) | Identification generation method, device, computer equipment and storage medium | |
| CN117520309A (en) | Cross-database type data migration method and device | |
| CN104052626A (en) | Method, device and system for configuring network element data | |
| CN106055582A (en) | Method and apparatus for replacing table name of database | |
| CN103593445A (en) | Data filling method and device | |
| CN115311399A (en) | Image rendering method and device, electronic equipment and storage medium | |
| CN104731863A (en) | Method for simplifying PostgreSQL division codes | |
| CN104750468A (en) | Server side topological data conversion method and device | |
| CN112559603B (en) | Feature extraction method, device, equipment and computer-readable storage medium | |
| CN105930104A (en) | Data storing method and device | |
| CN106933873A (en) | A kind of cross-platform data querying method and equipment | |
| CN112800242A (en) | Pedigree mining method and device, electronic equipment and computer-readable storage medium |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| GR01 | Patent grant | ||
| GR01 | Patent grant | ||
| CP03 | Change of name, title or address | ||
| CP03 | Change of name, title or address |
Address after: 310000 room 1001, building 2, No. 2, ZIJINGHUA Road, Xihu District, Hangzhou City, Zhejiang Province Patentee after: HANGZHOU MEGA TECHNOLOGY Co.,Ltd. Address before: 10, building 2, block B, The Union Buildings, No. 310013, Bauhinia Road, Hangzhou, Xihu District, Zhejiang Patentee before: Hangzhou Mijia Technology Co.,Ltd. |
|
| CP01 | Change in the name or title of a patent holder | ||
| CP01 | Change in the name or title of a patent holder |
Address after: Room 1001, building 2, No.2 ZIJINGHUA Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee after: Hangzhou Sikai Data Technology Group Co.,Ltd. Address before: Room 1001, building 2, No.2 ZIJINGHUA Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee before: HANGZHOU MEGA TECHNOLOGY Co.,Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20210412 Address after: 355200 no.181 erbatou, taimuyang village, Qinyu Town, Fuding City, Ningde City, Fujian Province Patentee after: Chen Ximei Address before: Room 1001, building 2, No.2 ZIJINGHUA Road, Xihu District, Hangzhou City, Zhejiang Province 310000 Patentee before: Hangzhou Sikai Data Technology Group Co.,Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20210607 Address after: 226000 affiliated houses of neighborhood committee of xiaoshiqiao community, Chongchuan District, Nantong City, Jiangsu Province Patentee after: Nantong Fengfeng Network Technology Co.,Ltd. Address before: 355200 no.181 erbatou, taimuyang village, Qinyu Town, Fuding City, Ningde City, Fujian Province Patentee before: Chen Ximei |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20231220 Address after: No. 313, Building 1, Fuchen Garden, Xinbei District, Changzhou City, Jiangsu Province, 213000 Patentee after: Universal World (Jiangsu) Information Technology Co.,Ltd. Address before: 226000 affiliated houses of neighborhood committee of xiaoshiqiao community, Chongchuan District, Nantong City, Jiangsu Province Patentee before: Nantong Fengfeng Network Technology Co.,Ltd. |
|
| TR01 | Transfer of patent right | ||
| TR01 | Transfer of patent right |
Effective date of registration: 20240229 Address after: Room 241, Building C1, Huijin International Plaza, Chongchuan District, Nantong City, Jiangsu Province, 226000 Patentee after: Nantong Tengmei Information Technology Co.,Ltd. Country or region after: China Address before: No. 313, Building 1, Fuchen Garden, Xinbei District, Changzhou City, Jiangsu Province, 213000 Patentee before: Universal World (Jiangsu) Information Technology Co.,Ltd. Country or region before: China |