- PostgreSQL Server Programming（Second Edition）
- Usama Dar Hannu Krosing Jim Mlodgenski Kirk Roybal
- 235字
- 2021-07-23 20:36:46
Moving beyond simple functions
Server programming can mean a lot of different things. Server programming is not just about writing server functions. There are many other things you can do in the server, which can be considered as programming.
Data comparisons using operators
For more complex tasks, you can define your own types, operators, and casts from one type to another, letting you actually compare apples and oranges.
As shown in the next example, you can define the type fruit_qty for fruit-with-quantity and then teach PostgreSQL to compare apples and oranges, say to make one orange to be worth 1.5 apples, in order to convert apples to oranges:
postgres=# CREATE TYPE FRUIT_QTY as (name text, qty int);
postgres=# SELECT '("APPLE", 3)'::FRUIT_QTY;
fruit_qty
----------------
(APPLE,3)
(1 row)
CREATE FUNCTION fruit_qty_larger_than(left_fruit FRUIT_QTY,right_fruit FRUIT_QTY)
RETURNS BOOL
AS $$
BEGIN
IF (left_fruit.name = 'APPLE' AND right_fruit.name = 'ORANGE')
THEN
RETURN left_fruit.qty > (1.5 * right_fruit.qty);
END IF;
IF (left_fruit.name = 'ORANGE' AND right_fruit.name = 'APPLE' )
THEN
RETURN (1.5 * left_fruit.qty) > right_fruit.qty;
END IF;
RETURN left_fruit.qty > right_fruit.qty;
END;
$$
LANGUAGE plpgsql;
postgres=# SELECT fruit_qty_larger_than('("APPLE", 3)'::FRUIT_QTY,'("ORANGE", 2)'::FRUIT_QTY);
fruit_qty_larger_than
-----------------------
f
(1 row)
postgres=# SELECT fruit_qty_larger_than('("APPLE", 4)'::FRUIT_QTY,'("ORANGE", 2)'::FRUIT_QTY);
fruit_qty_larger_than
-----------------------
t
(1 row)
CREATE OPERATOR > (
leftarg = FRUIT_QTY,
rightarg = FRUIT_QTY,
procedure = fruit_qty_larger_than,
commutator = >
);
postgres=# SELECT '("ORANGE", 2)'::FRUIT_QTY > '("APPLE", 2)'::FRUIT_QTY;
?column?
----------
t
(1 row)
postgres=# SELECT '("ORANGE", 2)'::FRUIT_QTY > '("APPLE", 3)'::FRUIT_QTY;
?column?
----------
f
(1 row)