Re: Beginner's questions about creating a custom data type in PostgreSQL...

On 7/24/06, Redefined Horizons <redefined(dot)horizons(at)gmail(dot)com> wrote:
> I've got a few questions about creating custom data types for PostgreSQL.

ok, i have a weakness for medieval stuff, so:

> I'm not sure how to phrase the questions without an example, so here goes:
>
> I'd like to create a custom data type representing a Medieval Knight.
> (This isn't what I really want to do, it's just an example.)

> One of the "properties" of this Medieval Knight data type that you can
> access through custom functions is "Combat Skill", which represents a
> Knights fighting ability.
>
> The Knight's fighting ability depends on two things. His horse, and
> his sword. The custom functions that work with the Medieval Knight
> data type know how to manipulate the properties of Horses and Swords
> as well.
>
> My Medieval Knight datatype "contains" a horse and sword. In the C
> programming language implementation of my datatype I would like to
> represent Swords and Horses with seperate structs, rather than
> throwing everything together into a single Medieval Knight struct.
>
> Is it possible to define the Sword and Horse structs in the same DLL
> that I define my Knight struct in, but not have Swords and Horses
> available as custom data types themselves?
>
> I want to have 3 separate structs for programming simplicity, but I
> want to embody them in a single custom data type and set of custom
> functions for PostgreSQL.

This is not necessarily a custom data type question, this is a data
modeling question. Your phrasing suggests that you have a programming
background...in the database world it's a bit different. The closest
thing to the custom type as you described it is the composite type.
Composite types are groupings of other types. When you create a
table, by the way, a composite type is automatically created for you.
you can also create your own using
http://www.postgresql.org/docs/8.1/static/sql-createtype.html (look at
the first form of create type).

so,
create table knight(combat_skill numeric, weapon text, horse text);

gives you a 'knight' type, now you can:

select (1.0, 'sword', 'trigger')::knight;

which composes the knight type out of the fields using a row
constructor. This isn't really necessary though, in most cases it's
safe just to use tables without consideration of its type at all, a
table is just a set of a type defined in its create table statement.
Let's say you wanted to develop the horse further...

create table horse(name text primary key, age int, speed numeric);

let's link the knight to his horse:

alter table knight add foreign key(horse) references horse;

this is not the only way to do this, but this is more of a relational
approach than what you suggested in your email. the key to
understanding sql and data modeling is reducing data to it's
functional dependencies so changes/additions to your data are as local
as possible. This is a little bit diffferent than you might think of
things from a C perspective.

merlin