How to write a custom struct data type for PostgreSQL using internal types

Question

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?

I suspect you actually want to create a Composite type, not a Base type. — Kevin
– Kevin, Commented Jun 30, 2017 at 4: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
– Craig Ringer, Commented Jun 30, 2017 at 5:11
In fact, doing it how you describe just won't work because the VARLENA header must come first. — Craig Ringer
– Craig Ringer, Commented Jun 30, 2017 at 5: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
– Sylvain, Commented Jun 30, 2017 at 6: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
– Sylvain, Commented Jun 30, 2017 at 6:48

Laurenz Albe · Accepted Answer · 2017-07-04 16:21:34Z

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.

answered Jul 4, 2017 at 16:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sylvain Over a year ago

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 Over a year ago

Could you expand a bit about the alignment problem? I have heard about this but I am not too familiar with it.

Laurenz Albe Over a year ago

See this Wikipedia article. 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.

Collectives™ on Stack Overflow

How to write a custom struct data type for PostgreSQL using internal types

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related