Creating instance of custom type

Someone asked me this recently and not playing with custom types much, I'm not sure this is the best solution. Anyrate here it goes. Note this works for PostgreSQL 8.2 and above. Note sure about 8.1 etc. I assume it probably does.

Let us say you created a custom type something like this:

CREATE TYPE my_type1 AS
   (name varchar(150),
    rotation_x double precision,
    rotation_y double precision,
    x_pos integer,
    y_pos integer
    );
    

	

First observe that a row object can be cast to a simple data type, because all table rows are really implemented as types in PostgreSQL. Therefore you can create an instance of this type by doing this:

SELECT CAST(ROW('motor', 0.5, 0, 10, 11) As my_type1) As an_instance;

Note you could write it using the more abbreviated PostgreSQL specific way, but I prefer the more verbose CAST since it exists in most relational databases

SELECT ROW('motor', 0.5, 0, 10, 11)::my_type1 As an_instance;

     an_instance
---------------------
 (motor,0.5,0,10,11)

If you wanted to select an item from this beast you would do:

SELECT (CAST(ROW('motor', 0.5, 0, 10, 11) As my_type1)).name As motor_name;


 motor_name
------------
 motor
(1 row)

If you wanted to select all the fields of the type you would do this:

SELECT (CAST(ROW('motor', 0.5, 0, 10, 11) As my_type1)).*;

 name  | rotation_x | rotation_y | x_pos | y_pos
-------+------------+------------+-------+-------
 motor |        0.5 |          0 |    10 |    11

Compound types

What if you had a compound type, how could you instantiate such a thing?

CREATE TYPE my_type2 AS
( compound_name varchar(150),
    right_item my_type1,
    left_item my_type1
);

SELECT CAST(ROW('superduper motor', charged, supercharged) as my_type2) As mycompound
FROM 
(SELECT CAST(ROW('motor1', 0.5, 0, 10, 11) As my_type1) As charged, 
CAST(ROW('motor2', 0, 0.5, 11, 12) As my_type1) As supercharged) As foo


                            mycompound
------------------------------------------------------------------
 ("superduper motor","(motor1,0.5,0,10,11)","(motor2,0,0.5,11,12)")
(1 row)

Pulling specific elements out

SELECT ((CAST(ROW('superduper motor', charged, supercharged) as my_type2)).right_item).name As r_charged_name
FROM 
(SELECT CAST(ROW('motor1', 0.5, 0, 10, 11) As my_type1) As charged, 
CAST(ROW('motor2', 0, 0.5, 11, 12) As my_type1) As supercharged) As foo;


 r_charged_name
------------
 motor1
(1 row)

Custom Constructors, Operators for custom types

Scott Bailey created a nice custom type example demonstrating how to create custom types and operators for your custom types.

Timespan Custom Type

Simon Greener over at SpatialDbAdvisor also has some custom types up his sleeve for PostGIS work. Check out his Vectorization: Exploding a linestring or polygon into individual vectors in PostGIS.