3 ms·

"Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among o

"Often the case for using custom data types, for example, is quite weak..." Lots of people come to the postgres community because of PostGIS, which is (among other things) a custom type distributed separately from postgres. Using custom types is not bad, the mistake is thinking that making a new type is easy. For a non-trivial custom data type, you need to tie it into the indexing system (GiST+KNN, GIN, SP-GiST, BTree, Hash) as well as the optimizer (have some good stats functions) -- not to mention the basics like a good representation and a well-thought out set of functions and operators. Custom data types are really one aspect of an extension, albeit a crucial one. So don't think about it as "hey, I'll make a new phone number type because it sounds cool", think about it like "we need better support for genomic data in postgres, let's sit down and plan a serious extension". If you want a special type for a phone number, use a domain over text with a check constraint. And that's SQL standard, too.

einhverfr 14y ago
The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.
- jeffdavis 14y ago
  "Now, it's rare that you are likely to get that into phone numbers..." That was part of my point -- if your entire business revolves around phone numbers, because you are a phone company or something, then maybe a custom data type makes sense. Then do it, and do it right. But if you think of a phone number as "something to call" then all you need to do is display it to the right person at the right time, and ensure that it reasonably matches something you can type on a phone. And that sounds more like a domain over a text field with a constraint, to me. "I use custom types a lot for defining output tuples for stored procedures" I don't really consider composite types "custom" types any more than an enum is a custom type. Maybe I misunderstood gfodor's statement.
  - einhverfr 14y ago
    With the ability to define columns which store composite types, the line between a custom data type in C and a composite type plus casts is blurring. Back in the 7.x line at one point I discovered that you could do something like: create table foo (...); create table bar (myfoo foo); And this table would be write-only, and indeed the backend would crash when reading from it. That's the only really interesting bug I ever found in PostgreSQL. However, I made the case that it would be nice to be able to treat tuples as full-fledged data types and now by at least 8.1, that was supported. So now I can: create type foo as (...); -- works with create table too create function foo_to_text .....; create function text_to_foo ....; create cast...... create table bar (my_foo foo); insert into bar (my_foo) values ('my.foo.as.text'::text); So the point is that composite types can now be used to blur the lines quite a bit between traditional custom types and the roles that composite types are traditionally used for. You can go a long way prototyping what is useful with composite types, and then later do that right in C.
- robe2 14y ago
  You don't need to create a custom type to return tuples anymore. (that kind of went out with 8.1). You can use OUT parameters or RETURNS TABLE syntax. RETURNS TABLE is ANSI compliant and supported since 8.3. SQL Server supports RETURNS TABLE as well.

einhverfr 14y ago

The reason to use a phone number type if you want to use one is that you can, in theory, create functions against it. A good example of what might be possible with such a type can be gained by looking at the standard networking type. Now, it's rare that you are likely to get that into phone numbers, but there might be cases where you could decompose the data and do relevant searches on components might come in really handy. The way I would look at doing that if I didn't want to go into C would be a complex type and a bunch of casts, functions, and operators. BTW, I use custom types a lot for defining output tuples for stored procedures, It is relatively hard to get away from that.

jeffdavis 14y ago
"Now, it's rare that you are likely to get that into phone numbers..." That was part of my point -- if your entire business revolves around phone numbers, because you are a phone company or something, then maybe a custom data type makes sense. Then do it, and do it right. But if you think of a phone number as "something to call" then all you need to do is display it to the right person at the right time, and ensure that it reasonably matches something you can type on a phone. And that sounds more like a domain over a text field with a constraint, to me. "I use custom types a lot for defining output tuples for stored procedures" I don't really consider composite types "custom" types any more than an enum is a custom type. Maybe I misunderstood gfodor's statement.
- einhverfr 14y ago
  With the ability to define columns which store composite types, the line between a custom data type in C and a composite type plus casts is blurring. Back in the 7.x line at one point I discovered that you could do something like: create table foo (...); create table bar (myfoo foo); And this table would be write-only, and indeed the backend would crash when reading from it. That's the only really interesting bug I ever found in PostgreSQL. However, I made the case that it would be nice to be able to treat tuples as full-fledged data types and now by at least 8.1, that was supported. So now I can: create type foo as (...); -- works with create table too create function foo_to_text .....; create function text_to_foo ....; create cast...... create table bar (my_foo foo); insert into bar (my_foo) values ('my.foo.as.text'::text); So the point is that composite types can now be used to blur the lines quite a bit between traditional custom types and the roles that composite types are traditionally used for. You can go a long way prototyping what is useful with composite types, and then later do that right in C.
robe2 14y ago
You don't need to create a custom type to return tuples anymore. (that kind of went out with 8.1). You can use OUT parameters or RETURNS TABLE syntax. RETURNS TABLE is ANSI compliant and supported since 8.3. SQL Server supports RETURNS TABLE as well.