The creation of PostgreSQL type and the use of type in a function

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 * $ ' 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 the 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 this 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)

The creation of PostgreSQL type and the use of type in a function