1

I am using PostgreSQL 9.6. I would like to create a new currency type extension on PostgreSQL that contains a string and a numerical value.

According to the documentation https://www.postgresql.org/docs/9.6/static/sql-createtype.html, I can use compose type directly in PostgreSQL, but this is not what I want because I want to be able to represent the type using a string and do casting. So the solution is to create a custom type such as the example "box" type. The documentation shows:

CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

CREATE TYPE box (
   INTERNALLENGTH = 16,
   INPUT = my_box_in_function,
   OUTPUT = my_box_out_function
);

But it's not really helpful as to HOW to write such a function. Further search shows that function can only be written in C and shows example of simple type, not struct type.

So I basically want something like

typedef struct {
    char code[4];
    Numeric  numeric;
} Currency

I want to use fixed type Numeric, that would avoid me the extra effort of implementing varlena header. Besides currency usually have fixed decimal point. I also want to be able to use the Numeric type internally because it allows me to reuse the function of Numeric.

How can I use Numeric, such that I can use a similar syntax as from PostgreSQL (e.g. Numeric(10,2)) to define my struct? Can I use the numeric_in function inside my currency_in function?

Sylvain
  • 542
  • 6
  • 20
  • 1
    I suspect you actually want to create a Composite type, not a Base type. – Kevin Jun 30 '17 at 04:45
  • Doing what you want this way is a lot of work. You'll need to define the operators, operator classes for index access, and lots more. You'll need a solid understanding of quite a bit of postgres's innards; user defined data types in C are not trivial to implement comprehensively. Consider a domain or composite type. – Craig Ringer Jun 30 '17 at 05:11
  • In fact, doing it how you describe just won't work because the VARLENA header must come first. – Craig Ringer Jun 30 '17 at 05:11
  • @Kevin Yes but with composite type I cannot have a custom ouput, nor input. I have to input stuff as ('USD', 1) instead of 'USD1' – Sylvain Jun 30 '17 at 06:47
  • @CraigRinger but do you need the varlena header if it's a fixed size Numeric type. Then the whole currency type is of fixed size too. – Sylvain Jun 30 '17 at 06:48
  • You're trying to put application logic in your database. Don't do that. Put the parsing logic in your application instead. – Kevin Jun 30 '17 at 06:55
  • @Kevin There is no application logic in this, just pure type definition. Just like PostgreSQL has a money type, I am thinking a currency type can be useful as well. – Sylvain Jun 30 '17 at 06:58
  • Besides I am not even mentioning any application in my question. Just writing a PostgreSQL extension. – Sylvain Jun 30 '17 at 07:06
  • @Sylvain `NUMERIC` is an arbitrary-length binary-coded decimal string, so it's `VARLENA`. – Craig Ringer Jun 30 '17 at 07:40
  • @CraigRinger back to my original question, is there a way to declare a fixed size numeric type as part of my struct so that my variable definition has a fixed size? I am well aware it is a lot of work :) – Sylvain Jun 30 '17 at 07:55
  • @CraigRinger I think I get what you mean. Since struct must be of known size upon compilation time, there is no way to know at that time what will be the size of my structure, since I cannot specify what precision I want on my Numeric at this moment. – Sylvain Jun 30 '17 at 09:37
  • @Sylvain Postgres's `NUMERIC` functions would also have no idea how to work with it because it'd lack the appropriate header. – Craig Ringer Jun 30 '17 at 10:11

1 Answers1

2

Both character and numeric are varlena, so you won't be able to store them in a fixed length data structure.

I would define the data type as

typedef struct {
    /* value times 100 */
    int64 value;
    /* currency name */
    char denomination[4];
} currency;

Storing the characters last avoids alignment problems.

Operations on the value are now simple integer operations. I don't expect that you want to multiply or devide two currency values; that wouldn't make much sense.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Interesting solution, yes I had a look at the code, it seems the money type shares a similar implementation (use integer for storing value). – Sylvain Jul 06 '17 at 02:53
  • Could you expand a bit about the alignment problem? I have heard about this but I am not too familiar with it. – Sylvain Jul 06 '17 at 02:53
  • 1
    See [this Wikipedia article](https://en.wikipedia.org/wiki/Data_structure_alignment#Data_structure_padding). I was sloppy with what I said; it's more a problem of wasted space. If the `char` field is 4 bytes long, 4 bytes might be wasted on some platforms due to padding. – Laurenz Albe Jul 07 '17 at 07:10