Creating a custom PostgreSQL type that is a set of other types?

The kind of information I want to store in this type is, for example: "330.30 eur/ton" or "25.15 usd/kg".

So, basically:

• Price as decimal.
• Currency as a FK to another table.
• Weight unit as a F开发者_开发知识库K to another table.

When making queries I would like to access the members directly e.g. "selling_price.price" or "selling_price.currency".

And I would like to be able to sort by this field as well... Which creates a whole host of problems, I realize, as we need to convert currencies and weight units.

One solution would be to add a fourth element, which stores the same information in a standardized format (e.g. usd/kg as a float), sort by that, and then update this whenever we updated currency exchange rates.

So, basically, this custom type (let's call it PRICE_PER_UNIT) would store:

• Price as decimal.
• Currency as FK.
• Weight unit as a FK.
• Value in standardized format.

And sorting on this field would have to be done by the standardized field, as a float.

Since I have never done anything like it in SQL before, I have no idea how to even get started with any of this... Anyone?

---

PostgreSQL supports CREATE TYPE for creating composite types. I think that's the best place for you to start.

---

Unless you're using the beta 9.1 version, avoid PostgreSQL types like the plague. They're a huge pain to update, and it's not until 9.1 that you can add or remove values from enums or alter attributes. For other versions, you're limited to renaming, changing the owner, and changing the schema.

You may want to be wary of storing the "standardized" price for sorting. Here I'm assuming you're talking about storing everything as (say) US dollars per kilogram. Anytime the currency exchange rates change, you're going to have to update every row in your table. Instead, you may want to store the conversion factor for the currency into USD, and store the conversion factor for the weight into kg, then sort based on an calculated value (conversion factors from the currency and weight type tables are joined to your data table).