Thread: self defined data type "with limit"?
Hello everyone,
I have created a new data type "mychar". How can I specify a limit for it?
This (unlimited version) works fine:
create table table_a(col_a mychar);
This gives an error:
create table table_a(col_a mychar(10));
ERROR: syntax error at or near "(" bei Zeichen 34
ZEILE 1: create table table_a(col_a mychar(10));
Background: I need a varchar(10) but with quasi numeric sort order
so that "2" appears before "10".
Therefore I thought I create a new type and a new operator class.
Any better way to perform this?
Regards,
Michael
--
WINCOR NIXDORF International GmbH
Retail Store Solutions
Wernerwerkdamm 16
13629 Berlin, Germany
Phone +49 (0) 30 5017-1386
Fax +49 (0) 30 5017-1305
E-Mail Michael.Enke@wincor-nixdorf.com
WINCOR NIXDORF International GmbH
Sitz der Gesellschaft: 33106 Paderborn, Heinz-Nixdorf-Ring 1
Registergericht Paderborn HRB 3507
Geschäftsführer: Eckard Heidloff (Vorsitzender), Stefan Auerbach, Jürgen Wilde, Dr. Jürgen Wunram
Vorsitzender des Aufsichtsrats: Karl-Heinz Stiller
Steuernummer: 339/5884/0031
Ust-ID Nr.: DE812927716
WEEE-Reg.-Nr. DE44477193
Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder
diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
This e-mail may contain confidential information. If you are not the intended recipient or
have received this e-mail in error, please notify the sender immediately and destroy this e-mail.
Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden.
On Wed, Jun 27, 2007 at 02:08:43PM +0200, Michael Enke wrote: > Hello everyone, > I have created a new data type "mychar". How can I specify a limit for it? > > This (unlimited version) works fine: > create table table_a(col_a mychar); What you want is called "user defined typmod" and I don't beleive any released version has it, but it will be in the next release. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Martijn van Oosterhout wrote:
> On Wed, Jun 27, 2007 at 02:08:43PM +0200, Michael Enke wrote:
>> Hello everyone,
>> I have created a new data type "mychar". How can I specify a limit for it?
>>
>> This (unlimited version) works fine:
>> create table table_a(col_a mychar);
>
> What you want is called "user defined typmod" and I don't beleive any
> released version has it, but it will be in the next release.
I believe he could do it with a domain.
create domain myreal_char as varchar(50);
create table table_a(col_a myreal_char);
Joshua D. Drake
>
> Have a nice day,
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
Joshua D. Drake wrote:
> Martijn van Oosterhout wrote:
>
>> On Wed, Jun 27, 2007 at 02:08:43PM +0200, Michael Enke wrote:
>>
>>> Hello everyone,
>>> I have created a new data type "mychar". How can I specify a limit
>>> for it?
>>>
>>> This (unlimited version) works fine:
>>> create table table_a(col_a mychar);
>>
>>
>> What you want is called "user defined typmod" and I don't beleive any
>> released version has it, but it will be in the next release.
>
>
> I believe he could do it with a domain.
>
> create domain myreal_char as varchar(50);
> create table table_a(col_a myreal_char);
My primary goal is to get quasi numeric ordering on text column, e.g.
1
2
10
Normal order with varchar would be
1
10
2
I can do it of course with lpad:
select * from table_a where lpad(col_a,18,'0') > lpad('12345',18,'0') order by lpad(col_a,18,'0');
but in this case no index can be used.
So I created my own type and operator classes and all works as I want (with use of index)
with the small exception that I can not specify a limit aka "user defined typmod".
Probably this is what I'm looking for.
Thanks,
Michael
Michael Enke wrote: > My primary goal is to get quasi numeric ordering on text column, e.g. > 1 > 2 > 10 > Normal order with varchar would be > 1 > 10 > 2 You don't need to custom type for that. A custom operator class with custom comparison operators is enough. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Michael Enke wrote: > >> My primary goal is to get quasi numeric ordering on text column, e.g. >> 1 >> 2 >> 10 >> Normal order with varchar would be >> 1 >> 10 >> 2 > > > You don't need to custom type for that. A custom operator class with > custom comparison operators is enough. > Ok, I tried with ordinary varchar and my own operators/op class. But than: 1) the index is never used (I created it 'with' my opclass) 2) the 'order by' doesn't care about my operator class, it's normal varchar sequence. Regards, Michael
Michael Enke <michael.enke@wincor-nixdorf.com> writes:
> Heikki Linnakangas wrote:
>> You don't need to custom type for that. A custom operator class with
>> custom comparison operators is enough.
> Ok, I tried with ordinary varchar and my own operators/op class. But than:
> 1) the index is never used (I created it 'with' my opclass)
> 2) the 'order by' doesn't care about my operator class, it's normal varchar sequence.
Yeah, because ORDER BY is still going to look to the default varchar
opclass to determine what the ordering is supposed to be. Assuming
your custom less-than operator is named <<<, you'd have to writeORDER BY col USING <<<
to get this sort order.
If you want ORDER BY on the column to default to your custom ordering,
the only way is a distinct datatype that you can make your custom
opclass be the default for.
The domain idea might work, I'm not totally sure. Defining
functions/operators on a domain is a bit ticklish because anything but
an exact match will get smashed to the domain base type and thus not
match your function. I think though that in this case you might get
away with it because it would be an exact match --- it's worth a try
anyway.
regards, tom lane
> If you want ORDER BY on the column to default to your custom ordering,
> the only way is a distinct datatype that you can make your custom
> opclass be the default for.
>
> The domain idea might work, I'm not totally sure. Defining
> functions/operators on a domain is a bit ticklish because anything but
> an exact match will get smashed to the domain base type and thus not
> match your function. I think though that in this case you might get
> away with it because it would be an exact match --- it's worth a try
> anyway.
I gave it a try but
a) my original problem appeared: I can not specify a limit for a domain type:
db=> create domain vchar as varchar; CREATE DOMAIN db=> create temp table a(a vchar(10)); ERROR: syntax error at
ornear "(" at character 28 LINE 1: create temp table a(a vchar(10));
b) the sort order is the same as for varchar with my custom opclass (creating with default for type vchar).
So I will end up with my own type (for in/out functions simply calling that ones from varchar)
and using "user defined typmod" if available in an upcoming release.
Would it be a nice feature for standard postgres to support such a type? ;-)
The compare function simply would be:
if(len_left < len_right) return -1;
else if(len_left > len_right) return 1;
else return strncmp(left, right, len_left);
Thank you for discussion,
Michael