创建一个person的数据类型

sky=# create type person as (name varchar(10), age int);
CREATE TYPE
sky=#

使用person这个数据类型

sky=# create table use_person (id serial, o person);
CREATE TABLE
sky=#

插入示例数据

sky=# insert into use_person (o) values (row('yzy', 18));
INSERT 0 1
sky=# insert into use_person (o) values (row('girl', 19));
INSERT 0 1
sky=# select * from use_person;
 id |     o
----+-----------
  1 | (yzy,18)
  2 | (girl,19)
(2 rows)

sky=#

定义比较运算符

这里只定义一种，就是<比较运算符。定义如下:

CREATE OR REPLACE FUNCTION person_age_smaller(left_o person, right_o person) RETURNS BOOLEAN AS
$$
  SELECT (left_o).age < (right_o).age
$$
LANGUAGE sql;

CREATE OPERATOR <(PROCEDURE = person_age_smaller, LEFTARG = person, RIGHTARG = person, COMMUTATOR = <);

使用

sky=# SELECT (SELECT o from use_person WHERE id = 1) < (SELECT o from use_person WHERE id = 2);
 ?column?
----------
 t
(1 row)

sky=# SELECT (SELECT o from use_person WHERE id = 2) < (SELECT o from use_person WHERE id = 1);
 ?column?
----------
 f
(1 row)

sky=#