PostgreSQL Type的建立與Type在函數中的使用

標籤：

postgres=# create type complex as(
postgres(# r double precision,
postgres(# i double precision
postgres(# );
CREATE TYPE
postgres=# create type inventory_item as(
postgres(# name text,
postgres(# supplier_id integer,
postgres(# price numeric);
CREATE TYPE
postgres=# create table on_hand(
postgres(# item inventory_item,
postgres(# count integer
postgres(# );
CREATE TABLE
postgres=# insert into on_hand values (ROW(‘fuzzy dice‘,42,1.99),1000);
INSERT 0 1
postgres=# create function price_extension(inventory_item,integer) returns numeric
postgres-# as ‘sel

postgres-# as ‘select $1.price * $2‘ language sql;
CREATE FUNCTION
postgres=# select price_extension(item,10) from on_hand;
price_extension
-----------------
19.90
(1 row)

postgres=# insert into on_hand values ((‘Apple‘,22,4.4),2000);
INSERT 0 1
postgres=# select * from on_hand ;
item | count
------------------------+-------
("fuzzy dice",42,1.99) | 1000
(Apple,22,4.4) | 2000
(2 rows)

postgres=# insert into on_hand values (row(‘Apple‘,22,4.4),2000);
INSERT 0 1
postgres=# select * from on_hand ;
item | count
------------------------+-------
("fuzzy dice",42,1.99) | 1000
(Apple,22,4.4) | 2000
(Apple,22,4.4) | 2000
(3 rows)

postgres=# insert into on_hand values (ROW(‘Apple‘,22,4.4),2000);
INSERT 0 1
postgres=# select * from on_hand ;
item | count
------------------------+-------
("fuzzy dice",42,1.99) | 1000
(Apple,22,4.4) | 2000
(Apple,22,4.4) | 2000
(Apple,22,4.4) | 2000
(4 rows)

postgres=# insert into on_hand values (ROW(‘Orange‘,22,55),3000);
INSERT 0 1
postgres=# insert into on_hand values (ROW(‘Orange1‘,22,66),3000);
INSERT 0 1

postgres=# \d on_hand
Table "public.on_hand"
Column | Type | Modifiers
--------+----------------+-----------
item | inventory_item |
count | integer |

postgres=# select item from on_hand ;
item
------------------------
("fuzzy dice",42,1.99)
(Apple,22,4.4)
(Apple,22,4.4)
(Apple,22,4.4)
(Orange,22,55)
(Orange1,22,66)
(6 rows)

postgres=# select (item).name from on_hand where (item).price >10;
name
---------
Orange
Orange1
(2 rows)

postgres=# select (item).name from on_hand ;
name
------------
fuzzy dice
Apple
Apple
Apple
Orange
Orange1
(6 rows)

postgres=# select (on_hand.item).name from on_hand where (on_hand.item).price > 10;
name
---------
Orange
Orange1
(2 rows)

select just one field from the result of a function that returns a composite value,you‘d need to

write something like:

select (my_func(...)).field from table_name;

postgres=# create table complex_col (col complex);
CREATE TABLE

postgres=# insert into complex_col values ((1.1,2.2));
INSERT 0 1

postgres=# insert into complex_col (col.r,col.i) values (8.8,9.9);
INSERT 0 1
postgres=# select * from complex_col ;
col
-----------
(1.1,2.2)
(8.8,9.9)
(2 rows)

PostgreSQL Type的建立與Type在函數中的使用