sudo apt install build-essential zlib1g-dev libreadline-dev -y
 
wget https://ftp.postgresql.org/pub/source/v10.6/postgresql-10.6.tar.gz
 
tar xvfz postgresql-10.6.tar.gz
 
cd postgresql-10.6
./configure
make
sudo make install
 
sudo apt install postgresql-contrib
sudo passwd postgres
su postgres
psql

PersonName 类型值的 BNF

BNF（巴科斯范式，Backus-Naur Form）是一种用于描述编程语言语法的正式语法。

PersonName ::= Family','Given | Family', 'Given
 
Family     ::= NameList
Given      ::= NameList
 
NameList   ::= Name | Name' 'NameList
 
Name       ::= Upper Letters
 
Letter     ::= Upper | Lower | Punc
 
Letters    ::= Letter | Letter Letters
 
Upper      ::= 'A' | 'B' | ... | 'Z'
Lower      ::= 'a' | 'b' | ... | 'z'
Punc       ::= '-' | "'"

编写

这里拿 Complexpostgres/src/tutorial at master · postgres/postgres · GitHub举例

安装依赖

sudo apt-get install postgresql-server-dev-all

编译运行生成 complex.so/complex.sql

make

将.so 文件 copy 到 postgresql 共享库目录下

sudo cp complex.so /usr/lib/postgresql/14/lib/

然后连接到 postgresql

psql -h localhost -U postgres -d postgres

运行 complex.sql 内容，即可使用。

自定义 PersonName 类型效果

Private Code

LINE 1: INSERT INTO person (name) VALUES ('Jesus');
                                          ^
postgres=# INSERT INTO person (name) VALUES ('Smith  ,  Harold');
ERROR:  invalid input syntax for type PersonName: "Smith  ,  Harold"
LINE 1: INSERT INTO person (name) VALUES ('Smith  ,  Harold');
                                          ^
postgres=# INSERT INTO person (name) VALUES ('Gates, William H., III');
ERROR:  invalid input syntax for type PersonName: "Gates, William H., III"
LINE 1: INSERT INTO person (name) VALUES ('Gates, William H., III');
                                          ^
postgres=# INSERT INTO person (name) VALUES ('A,B C');
ERROR:  invalid input syntax for type PersonName: "A,B C"
LINE 1: INSERT INTO person (name) VALUES ('A,B C');
                                          ^
postgres=# INSERT INTO person (name) VALUES ('Smith, john');
ERROR:  invalid input syntax for type PersonName: "Smith, john"
LINE 1: INSERT INTO person (name) VALUES ('Smith, john');
 
postgres=# SELECT family('Jonh,Data'::PersonName);
 family
--------
 Jonh
(1 row)
 
postgres=# SELECT given('Jonh,Data'::PersonName);
 given
-------
 Data
(1 row)
 
postgres=# SELECT show('Jonh,Data'::PersonName);
   show
-----------
 Data Jonh
(1 row)
 
 
postgres=# INSERT INTO person (name) VALUES ('Smith, John'), ('Smith, John'), ('O''Brien, Patrick Sean'), ('Mahagedara Patabendige, Minosha Mitsuaki Senakasiri'), ('I-Sun, Chen Wang'), ('Clifton-Everest, Charles Edward');
INSERT 0 6
 
postgres=# CREATE INDEX idx_person_name_hash ON person USING hash (name pname_hash_ops);
CREATE INDEX

如何在 PostgreSQL 中实现一个自定义类型

PostgreSQL

Install

手动编译

PersonName 类型值的 BNF

编写

自定义 PersonName 类型效果

参考

Comments (0)

Send comment