Thread: User-defined typle similar to char(length) varchar(length)
Is it possible to create user-defined type with optional length in create table similar to char()/varchar()/bit()? Without modification gram.y of course... Thank you. -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
On Mon, Jul 31, 2006 at 05:04:00PM +0400, Teodor Sigaev wrote: > Is it possible to create user-defined type with optional length in create > table similar to char()/varchar()/bit()? Without modification gram.y of > course... No. Search the archives for discussions about "user defined typmod". The basic problem came down too that the set of allowed words for functions and types would be forced to be the same (due to restrictions in lookahead), and people wern't happy with that because if may hamper future SQL compatability. There were ways around this (all fairly ugly though). Note: this is what I remember about the discussions anyway. I'm sure a lot of people would be happy if someone took this on though. 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 <kleptog@svana.org> writes:
> No. Search the archives for discussions about "user defined typmod".
> The basic problem came down too that the set of allowed words for
> functions and types would be forced to be the same (due to restrictions
> in lookahead), and people wern't happy with that because if may hamper
> future SQL compatability. There were ways around this (all fairly ugly
> though).
I seem to remember that someone had come up with an idea that might
allow it to work, but no one pushed it as far as coming up with a patch.
It's definitely the grammar that is the problem though, specifically
constructs like
char(42) 'literal value here'
If you don't treat the names of these types as reserved, it is darn hard
to tell that you're not looking at a function call until you get to the
right paren and see a string literal as lookahead ... and postponing the
parse decision that long is painful.
regards, tom lane
> char(42) 'literal value here'
>
Playing around that I noticed:
postgres=# select '{asd}'::text[], '{asd}'::_text, _text '{asd}'; text | _text | _text
-------+-------+------- {asd} | {asd} | {asd}
(1 row)
postgres=# select text[] '{asd}';
ERROR: syntax error at or near "]"
LINE 1: select text[] '{asd}';
Is it desired effect?
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
Teodor Sigaev <teodor@sigaev.ru> writes:
> postgres=# select text[] '{asd}';
> ERROR: syntax error at or near "]"
> LINE 1: select text[] '{asd}';
> Is it desired effect?
Not really, but that's another thing that seems unreasonably hard to
fix.
regards, tom lane
> Not really, but that's another thing that seems unreasonably hard to
> fix.
>
Sorry, but more problem:
1)
postgres=# select 'as'::pg_catalog.char, 'as'::char, 'as'::char(2); char | bpchar | bpchar
------+--------+-------- a | a | as
(1 row)
postgres=# select 'as'::pg_catalog.char(2);
ERROR: syntax error at or near "("
LINE 1: select 'as'::pg_catalog.char(2);
2)
postgres=# select char(2) 'as'; bpchar
-------- as
(1 row)
postgres=# select pg_catalog.char(2) 'as';
ERROR: syntax error at or near "'as'"
LINE 1: select pg_catalog.char(2) 'as';
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
On Tue, Aug 01, 2006 at 06:47:31PM +0400, Teodor Sigaev wrote: > >Not really, but that's another thing that seems unreasonably hard to > >fix. > > > Sorry, but more problem: <snip more problems> No kidding, it's quite a mess and really needs to be cleaned up. About the only thing we could do would be to merge the productions for types and functions. Or at least, make the productions look similar enough that bison can avoid deciding which it is until it's got past the whole definition. Habe 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 <kleptog@svana.org> writes:
> <snip more problems>
> No kidding, it's quite a mess and really needs to be cleaned up. About
> the only thing we could do would be to merge the productions for types
> and functions. Or at least, make the productions look similar enough
> that bison can avoid deciding which it is until it's got past the whole
> definition.
Yeah. There are previous discussions about this in the archives.
What we've got now is the best idea that anyone had back in the 7.3
or so timeframe ... but feel free to improve it if you can.
regards, tom lane
> or so timeframe ... but feel free to improve it if you can. I'm not very familiar with yacc/bison, so pls, review attached patch. I may miss something... It's based on ideas in previous discussions: http://www.pgsql.ru/db/mw/msg.html?mid=1995063 http://www.pgsql.ru/db/mw/msg.html?mid=2091842 Patch adds support of typmod to any type, support of typmod to format_type(). I partially make typename and function name as different set except forms like "select TYPE 'asd'" or "select TYPE(N) 'asd'". Type modifier can be only one integer >=0, however grammar rules allow it to be list of expressions. It was done for simplify far future :) -- Teodor Sigaev E-mail: teodor@sigaev.ru WWW: http://www.sigaev.ru/
Attachment
I'm surprised you got the patch so small. Mind you, you didn't do any folding in the productions for NUMERIC and CHAR which in the long term would probably need to be done. Also, there's the issue of converting the arguments to a typmod, in the long term it'd have to be user-defined per type. Still, it looks good so far, just some way to go still... Have a nice day, On Thu, Aug 03, 2006 at 10:24:43PM +0400, Teodor Sigaev wrote: > >or so timeframe ... but feel free to improve it if you can. > > I'm not very familiar with yacc/bison, so pls, review attached patch. I may > miss something... It's based on ideas in previous discussions: > http://www.pgsql.ru/db/mw/msg.html?mid=1995063 > http://www.pgsql.ru/db/mw/msg.html?mid=2091842 > > Patch adds support of typmod to any type, support of typmod to > format_type(). > I partially make typename and function name as different set except forms > like > "select TYPE 'asd'" or "select TYPE(N) 'asd'". > > Type modifier can be only one integer >=0, however grammar rules allow it > to be list of expressions. It was done for simplify far future :) > > > -- > Teodor Sigaev E-mail: teodor@sigaev.ru > WWW: > http://www.sigaev.ru/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- 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 <kleptog@svana.org> writes:
> I'm surprised you got the patch so small. Mind you, you didn't do any
> folding in the productions for NUMERIC and CHAR which in the long term
> would probably need to be done.
Yeah, the patch ought to be making the grammar smaller not bigger.
> Also, there's the issue of converting
> the arguments to a typmod, in the long term it'd have to be
> user-defined per type.
I think we could legislate that the stored typmod is the same as what
the user sees (and can't be negative). The fact that it's different
for some of the built-in types is a historical artifact that I'd love
to get rid of.
regards, tom lane
On Thu, Aug 03, 2006 at 04:18:53PM -0400, Tom Lane wrote: > > Also, there's the issue of converting > > the arguments to a typmod, in the long term it'd have to be > > user-defined per type. > > I think we could legislate that the stored typmod is the same as what > the user sees (and can't be negative). The fact that it's different > for some of the built-in types is a historical artifact that I'd love > to get rid of. But that makes NUMERIC(x,y) impossible to represent. That probably ok I guess. I was just wondering if it would be reasonable to allow users to create a currency type whose precision can be specified the same way as for numeric. 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 <kleptog@svana.org> writes:
> On Thu, Aug 03, 2006 at 04:18:53PM -0400, Tom Lane wrote:
>> I think we could legislate that the stored typmod is the same as what
>> the user sees (and can't be negative). The fact that it's different
>> for some of the built-in types is a historical artifact that I'd love
>> to get rid of.
> But that makes NUMERIC(x,y) impossible to represent.
Well, we have to special-case INTERVAL anyway (because its cramming some
truly bizarre things into typmod), and it wouldn't bother me too much to
special-case NUMERIC as well.
Another option is to agree on some simple rule for cramming two values
into one typmod, like first one in the low half and second in the high
half, and then user types could have either one or two typmod values ---
but I can imagine some pretty bizarre behavior if the type is expecting
one value and you enter two or vice versa. NUMERIC can finesse this
because the default for scale is zero, but in the general case that
wouldn't work so well.
Does anyone have examples of real user-defined types that would need two
fields? If not it may not be worth spending time on.
regards, tom lane
On Thu, Aug 03, 2006 at 05:04:47PM -0400, Tom Lane wrote: > Martijn van Oosterhout <kleptog@svana.org> writes: > > On Thu, Aug 03, 2006 at 04:18:53PM -0400, Tom Lane wrote: > >> I think we could legislate that the stored typmod is the same as what > >> the user sees (and can't be negative). The fact that it's different > >> for some of the built-in types is a historical artifact that I'd love > >> to get rid of. > > > But that makes NUMERIC(x,y) impossible to represent. > > Well, we have to special-case INTERVAL anyway (because its cramming some > truly bizarre things into typmod), and it wouldn't bother me too much to > special-case NUMERIC as well. > > Another option is to agree on some simple rule for cramming two values > into one typmod, like first one in the low half and second in the high > half, and then user types could have either one or two typmod values --- > but I can imagine some pretty bizarre behavior if the type is expecting > one value and you enter two or vice versa. NUMERIC can finesse this > because the default for scale is zero, but in the general case that > wouldn't work so well. > > Does anyone have examples of real user-defined types that would need two > fields? If not it may not be worth spending time on. I can think of histograms as a data type which may take more than one argument, maybe even an array for boundary information. I think the direction *in the long term* should be to allow multiple arguments (as a ROW type?) and other base or complex types as arguments. The value would be a type itself and the datatype must do the right thing regarding it. This may not be practical for short-term, but would open up initialization parameters for user-defined typed. --elein > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend >
>> But that makes NUMERIC(x,y) impossible to represent.
> Well, we have to special-case INTERVAL anyway (because its cramming some
> truly bizarre things into typmod), and it wouldn't bother me too much to
> special-case NUMERIC as well.
We have a lot of special transformation of type based on typmod (char, bit,
float), a lot of additional keywords ("national", "varying" etc), a lot of
hardcoded synonyms (real->float4 etc). BPchar typemod stores value with added
VARHRDSZ. Sorry, I don't see regular way to support those exceptions even with
typmod_in/typemod_out functions per type...
User defined type can check typmod option in its input or cast function, I don't
think that will be very expensive.
> one value and you enter two or vice versa. NUMERIC can finesse this
> because the default for scale is zero, but in the general case that
> wouldn't work so well.
I agree.
Is there a chance to commit this patch to 8.2?
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
On Thu, Aug 03, 2006 at 05:37:21PM -0700, elein wrote: > I can think of histograms as a data type which may take more than one argument, > maybe even an array for boundary information. I think the direction *in the > long term* should be to allow multiple arguments (as a ROW type?) and other > base or complex types as arguments. The value would be a type itself and > the datatype must do the right thing regarding it. This may not be practical > for short-term, but would open up initialization parameters for user-defined > typed. Two problems: 1. The storage of the type cannot be affected by the typmod, because you're not going to always have it available. 2. It's got to be a single integer (int4 I think) So histograms would have to store the bounds some other way anyway... I think multiple arguments would be cool but we have to keep in mind the limitations of typmod. 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.
Teodor Sigaev <teodor@sigaev.ru> writes:
> Is there a chance to commit this patch to 8.2?
No. It's not within hailing distance of done, and by the time it was
done we'd be stretching the feature freeze deadline beyond all reason.
I encourage you to work on it with an eye to 8.3 though.
regards, tom lane
On Thu, 2006-08-03 at 17:04 -0400, Tom Lane wrote:
> Does anyone have examples of real user-defined types that would need two
> fields? If not it may not be worth spending time on.
>
What about if someone wanted to implement a relation as a type? I could
see perhaps something like:
CREATE TABLE ( ..., t RELATION('attr1:type1','attr2:type2',...)
);
If we allowed multiple arguments to the type and an arbitrary-length
typmod, that would seem to allow a lot of functionality. It would be
quite esoteric, but might be useful to meet a strange requirement.
Also, it might help people port a database to PostgreSQL. For instance,
if they are using MySQL and use the "enum" type, they might not have the
time to change all the database schema to be relational. Instead, they
could create their own enum type in postgresql and it could work the
same way.
Regards,Jeff Davis