Horton

Thread

  1. A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2005-08-28T17:07:27Z

    For a while I've been wondering about making a type that was really a
shell around a base type that tagged the type in some way. For example,
associating a currency with a numeric and complaining about additions
between mismatches.

Well, I did it and it's available here:
http://svana.org/kleptog/pgsql/taggedtypes.html

Below some examples of it in action. Yes, that's a timestamp that
remembers the timezone. Neat huh?

Tested on 7.4 and a recent 8.1devel so it should work for most people.
Installation reports welcome. Note, this is beta software, don't run it
on your production server. Thanks.

Have a nice day,


test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
 ?column?  
-----------
 10.10 USD
(1 row)

test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
ERROR:  Using operator +(currency,currency) with incompatable tags (USD,AUD)
test=# select c1, print_currency(c1) from c;
     c1     | print_currency 
------------+----------------
 232.44 USD | US$    232.44
 21.20 EUR  | ¤       21.20
 -13.44 AUD | AU$    -13.44
 0.01 USD   | US$      0.01
 14.00 AUD  | AU$     14.00
(5 rows)

test=# select 5.4*c1 from c where tag(c1) = 'AUD';
  ?column?  
------------
 -72.58 AUD
 75.60 AUD
(2 rows)

test=# select t, "timestamp"(t), date_part('hour',t) from c;
                    t                    |      timestamp      | date_part 
-----------------------------------------+---------------------+-----------
 2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 |         2
 2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |        10
 2005-08-14 02:00:00+02 Asia/Hong_Kong   | 2005-08-14 08:00:00 |         8
 2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |        20
 2005-08-14 02:00:00+02 Asia/Kuwait      | 2005-08-14 03:00:00 |         3
(5 rows)

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  2. Re: [GENERAL] A real currency type

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-03-21T13:49:18Z

    This looks very interesting.  Should we add it to the core distribution?

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> For a while I've been wondering about making a type that was really a
> shell around a base type that tagged the type in some way. For example,
> associating a currency with a numeric and complaining about additions
> between mismatches.
> 
> Well, I did it and it's available here:
> http://svana.org/kleptog/pgsql/taggedtypes.html
> 
> Below some examples of it in action. Yes, that's a timestamp that
> remembers the timezone. Neat huh?
> 
> Tested on 7.4 and a recent 8.1devel so it should work for most people.
> Installation reports welcome. Note, this is beta software, don't run it
> on your production server. Thanks.
> 
> Have a nice day,
> 
> 
> test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
>  ?column?  
> -----------
>  10.10 USD
> (1 row)
> 
> test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
> ERROR:  Using operator +(currency,currency) with incompatable tags (USD,AUD)
> test=# select c1, print_currency(c1) from c;
>      c1     | print_currency 
> ------------+----------------
>  232.44 USD | US$    232.44
>  21.20 EUR  | ?       21.20
>  -13.44 AUD | AU$    -13.44
>  0.01 USD   | US$      0.01
>  14.00 AUD  | AU$     14.00
> (5 rows)
> 
> test=# select 5.4*c1 from c where tag(c1) = 'AUD';
>   ?column?  
> ------------
>  -72.58 AUD
>  75.60 AUD
> (2 rows)
> 
> test=# select t, "timestamp"(t), date_part('hour',t) from c;
>                     t                    |      timestamp      | date_part 
> -----------------------------------------+---------------------+-----------
>  2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 |         2
>  2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |        10
>  2005-08-14 02:00:00+02 Asia/Hong_Kong   | 2005-08-14 08:00:00 |         8
>  2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |        20
>  2005-08-14 02:00:00+02 Asia/Kuwait      | 2005-08-14 03:00:00 |         3
> (5 rows)
> 
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +

  3. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-21T14:27:10Z

    On Tue, Mar 21, 2006 at 08:49:18AM -0500, Bruce Momjian wrote:
> 
> This looks very interesting.  Should we add it to the core distribution?

Excellent question. As yet I have received very little feedback on it,
though it does work as advertised. I have had some people complain that
while they'd like to use it (especially the timestamp-that-remembers-
the-timezone), they don't like the idea of an external module.

I suppose the are a few technical issues that could be raised, like the
fact that it searches user tables during the parse phase, but this is a
generic problem with non-immutable type input functions.

I'd like it to be considered for inclusion. If the interest is there I
can make any changes people suggest.

Have a nice day,

> 
> ---------------------------------------------------------------------------
> 
> Martijn van Oosterhout wrote:
> -- Start of PGP signed section.
> > For a while I've been wondering about making a type that was really a
> > shell around a base type that tagged the type in some way. For example,
> > associating a currency with a numeric and complaining about additions
> > between mismatches.
> > 
> > Well, I did it and it's available here:
> > http://svana.org/kleptog/pgsql/taggedtypes.html
> > 
> > Below some examples of it in action. Yes, that's a timestamp that
> > remembers the timezone. Neat huh?
> > 
> > Tested on 7.4 and a recent 8.1devel so it should work for most people.
> > Installation reports welcome. Note, this is beta software, don't run it
> > on your production server. Thanks.
> > 
> > Have a nice day,
> > 
> > 
> > test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
> >  ?column?  
> > -----------
> >  10.10 USD
> > (1 row)
> > 
> > test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
> > ERROR:  Using operator +(currency,currency) with incompatable tags (USD,AUD)
> > test=# select c1, print_currency(c1) from c;
> >      c1     | print_currency 
> > ------------+----------------
> >  232.44 USD | US$    232.44
> >  21.20 EUR  | ?       21.20
> >  -13.44 AUD | AU$    -13.44
> >  0.01 USD   | US$      0.01
> >  14.00 AUD  | AU$     14.00
> > (5 rows)
> > 
> > test=# select 5.4*c1 from c where tag(c1) = 'AUD';
> >   ?column?  
> > ------------
> >  -72.58 AUD
> >  75.60 AUD
> > (2 rows)
> > 
> > test=# select t, "timestamp"(t), date_part('hour',t) from c;
> >                     t                    |      timestamp      | date_part 
> > -----------------------------------------+---------------------+-----------
> >  2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 |         2
> >  2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |        10
> >  2005-08-14 02:00:00+02 Asia/Hong_Kong   | 2005-08-14 08:00:00 |         8
> >  2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |        20
> >  2005-08-14 02:00:00+02 Asia/Kuwait      | 2005-08-14 03:00:00 |         3
> > (5 rows)
> > 
> > -- 
> > Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > > tool for doing 5% of the work and then sitting around waiting for someone
> > > else to do the other 95% so you can sue them.
> -- End of PGP section, PGP failed!
> 
> -- 
>   Bruce Momjian   http://candle.pha.pa.us
>   SRA OSS, Inc.   http://www.sraoss.com
> 
>   + If your life is a hard drive, Christ can be your backup. +
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  4. Re: [GENERAL] A real currency type

    Pailloncy Jean-Gerard <jg@rilk.com> — 2006-03-21T17:15:29Z

    >> This looks very interesting.  Should we add it to the core  
>> distribution?
>
> Excellent question. As yet I have received very little feedback on it,
> though it does work as advertised. I have had some people complain  
> that
> while they'd like to use it (especially the timestamp-that-remembers-
> the-timezone), they don't like the idea of an external module.
>
> I suppose the are a few technical issues that could be raised, like  
> the
> fact that it searches user tables during the parse phase, but this  
> is a
> generic problem with non-immutable type input functions.
>
> I'd like it to be considered for inclusion. If the interest is there I
> can make any changes people suggest.
There was an old thread on the list about "extended type".

The general idea was to add a attribute to each column to represent  
the "unit" (as for physical measure).

I had worked on a development system that use this kind of "extended  
type" and it was very good to catch errors by checking the  
compatibility of extended type.

Cordialement,
Jean-Gérard Pailloncy

  5. Re: [GENERAL] A real currency type

    Josh Berkus <josh@agliodbs.com> — 2006-03-21T17:39:09Z

    Bruce, Martijin,

> > This looks very interesting.  Should we add it to the core distribution?
>
> Excellent question. As yet I have received very little feedback on it,
> though it does work as advertised. I have had some people complain that
> while they'd like to use it (especially the timestamp-that-remembers-
> the-timezone), they don't like the idea of an external module.

So, how about adding it to contrib for one version?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

  6. Re: [GENERAL] A real currency type

    Jim C. Nasby <jnasby@pervasive.com> — 2006-03-21T18:03:02Z

    On Tue, Mar 21, 2006 at 06:15:29PM +0100, Pailloncy Jean-Gerard wrote:
> >>This looks very interesting.  Should we add it to the core  
> >>distribution?
> >
> >Excellent question. As yet I have received very little feedback on it,
> >though it does work as advertised. I have had some people complain  
> >that
> >while they'd like to use it (especially the timestamp-that-remembers-
> >the-timezone), they don't like the idea of an external module.
> >
> >I suppose the are a few technical issues that could be raised, like  
> >the
> >fact that it searches user tables during the parse phase, but this  
> >is a
> >generic problem with non-immutable type input functions.
> >
> >I'd like it to be considered for inclusion. If the interest is there I
> >can make any changes people suggest.
> There was an old thread on the list about "extended type".
> 
> The general idea was to add a attribute to each column to represent  
> the "unit" (as for physical measure).
> 
> I had worked on a development system that use this kind of "extended  
> type" and it was very good to catch errors by checking the  
> compatibility of extended type.

While I'm not sure it makes sense to include such an 'extended type' in
the base system, it would be a neat project to have on pgFoundry.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  7. Re: [GENERAL] A real currency type

    Jim C. Nasby <jnasby@pervasive.com> — 2006-03-21T18:05:54Z

    On Tue, Mar 21, 2006 at 09:39:09AM -0800, Josh Berkus wrote:
> Bruce, Martijin,
> 
> > > This looks very interesting.  Should we add it to the core distribution?
> >
> > Excellent question. As yet I have received very little feedback on it,
> > though it does work as advertised. I have had some people complain that
> > while they'd like to use it (especially the timestamp-that-remembers-
> > the-timezone), they don't like the idea of an external module.
> 
> So, how about adding it to contrib for one version?

ISTM that having a currency type is pretty common for most databases; I
don't really see any reason not to just include it. Likewise for a type
that actually stores timezone info with a timestamp.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  8. [GENERAL] A real currency type

    Gregory Maxwell <gmaxwell@gmail.com> — 2006-03-21T19:00:14Z

    On 3/21/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> ISTM that having a currency type is pretty common for most databases; I
> don't really see any reason not to just include it. Likewise for a type
> that actually stores timezone info with a timestamp.

This really should be generalized to work with all the base types
because there are strong use cases for each. (timezones with
timestamps, currencies with numeric, physical units with floats and
ints)

  9. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-21T19:09:09Z

    On Tue, Mar 21, 2006 at 02:00:14PM -0500, Gregory Maxwell wrote:
> On 3/21/06, Jim C. Nasby <jnasby@pervasive.com> wrote:
> > ISTM that having a currency type is pretty common for most databases; I
> > don't really see any reason not to just include it. Likewise for a type
> > that actually stores timezone info with a timestamp.
> 
> This really should be generalized to work with all the base types
> because there are strong use cases for each. (timezones with
> timestamps, currencies with numeric, physical units with floats and
> ints)

Have you looked at the code? It *is* generalised. All you need to do is
provide a table of tags and indicate the base-type. Hence:

currency type = table of currencies + numeric base type
timestamp+zone type = table of timezones + timestamp base type
physical unit = table of units + numeric base type

These are examples already included in the code, but I'm sure people
can come up with more.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  10. Re: [GENERAL] A real currency type

    Peter Eisentraut <peter_e@gmx.net> — 2006-03-21T20:30:51Z

    Bruce Momjian wrote:
> This looks very interesting.  Should we add it to the core
> distribution?

I think such types would be better implemented as some sort of 
structured type, possibly with constructors and methods and all the 
other stuff that SQL talks about.  We don't have all of that yet -- 
maybe we don't need all of it immediately -- but before we start 
endorsing many of these types I'd like to see some consideration given 
to this issue.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

  11. Re: [GENERAL] A real currency type

    Andrew Dunstan <andrew@dunslane.net> — 2006-03-21T20:59:31Z

    Peter Eisentraut wrote:

>Bruce Momjian wrote:
>  
>
>>This looks very interesting.  Should we add it to the core
>>distribution?
>>    
>>
>
>I think such types would be better implemented as some sort of 
>structured type, possibly with constructors and methods and all the 
>other stuff that SQL talks about.  We don't have all of that yet -- 
>maybe we don't need all of it immediately -- but before we start 
>endorsing many of these types I'd like to see some consideration given 
>to this issue.
>
>  
>

Yeah, looks interesting but let's find the wrinkles. I was wondering if 
it might have relevance to what I wanted to do with enumeration types, 
i.e. we would tag each one with its particular enumeration id.

I also don't like the idea of it being done with user tables - if this 
is core material then the tags should go in the catalog, ISTM.

cheers

andrew

  12. Re: [GENERAL] A real currency type

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-21T21:10:21Z

    Martijn van Oosterhout <kleptog@svana.org> writes:
> I'd like it to be considered for inclusion.

The description page lists sufficiently many unresolved issues that
I'd have to call it "not ready for prime time" ... even assuming there
are not any issues you failed to identify.  One showstopper I can cite
immediately is the lack of any pg_dump support.

I could see putting this in contrib, perhaps, but to become part of core
it'd need to be better designed and better integrated.  That probably
means fixing some limitations in the current core code (for instance the
need for the auxiliary-type kluge).

Peter's point that there may exist SQL2003 features that cover the same
ground is also well taken ... we should investigate that before
inventing nonstandard stuff that we'll be stuck with supporting
forevermore.

			regards, tom lane

  13. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-21T22:12:12Z

    On Tue, Mar 21, 2006 at 04:10:21PM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > I'd like it to be considered for inclusion.
> 
> The description page lists sufficiently many unresolved issues that
> I'd have to call it "not ready for prime time" ... even assuming there
> are not any issues you failed to identify.  One showstopper I can cite
> immediately is the lack of any pg_dump support.

Really? The code creates ordinary types, operators and functions, all
of which are dumped fine by pg_dump. Dependancies are created which
should ensure the parts get dumped in the right order. What special
support in pg_dump were you envisioning?

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  14. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-21T22:23:59Z

    On Tue, Mar 21, 2006 at 03:59:31PM -0500, Andrew Dunstan wrote:
> >I think such types would be better implemented as some sort of 
> >structured type, possibly with constructors and methods and all the 
> >other stuff that SQL talks about.  We don't have all of that yet -- 
> >maybe we don't need all of it immediately -- but before we start 
> >endorsing many of these types I'd like to see some consideration given 
> >to this issue.
> 
> Yeah, looks interesting but let's find the wrinkles. I was wondering if 
> it might have relevance to what I wanted to do with enumeration types, 
> i.e. we would tag each one with its particular enumeration id.

I have considered making enumeration types with this. It would probably
work but kind of overkill. The main problem being that this code is
seriously aimed at creating new types that you intend to do operations
on, create operators for. An enumeration is just that, an enumeration.

> I also don't like the idea of it being done with user tables - if this 
> is core material then the tags should go in the catalog, ISTM.

But the user is providing the list of tags when creating the type.
Unless you are proposing copying the tag table to the catalog when the
type is created? You can't store all the tags for all tagged types in
one tag table either. At least, that wasn't what I was envisiging. See
the currency type where the tag-table also stores the format string for
output. No other tagged type is going to need that information.

SQL types with constructors and methods might provide this also, I
don't know. My purpose with this was allowing people to simply create
new types with specific behaviour without writing functions in C. Right
now type input/output functions cannot be written in anything other
than C. Here are a few C functions that handle these new types in a
generic way and user can write little pl/pgsql wrappers to customise
the output.

Have a ncie day,

-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  15. Re: [GENERAL] A real currency type

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-21T22:25:15Z

    Martijn van Oosterhout <kleptog@svana.org> writes:
> Really? The code creates ordinary types, operators and functions, all
> of which are dumped fine by pg_dump. Dependancies are created which
> should ensure the parts get dumped in the right order. What special
> support in pg_dump were you envisioning?

The dump should look the same as the commands originally used to create
the type, which is surely not going to happen with that "SELECT
create_tagged_type()" stuff barring pg_dump modifications.  Otherwise
we are nailing down not one but two representations of this feature that
we'll have to support forevermore: what the users see and what's in
pg_dump scripts.  We've already learned that lesson the hard way several
times, and are still trying to cope with the fallout in some places
(serial columns for instance).

Now I happen to think that SELECT create_tagged_type() is a horrid kluge
anyway ;-) so I'm not proposing that pg_dump be changed to output that.
What we would need is to design a command syntax that we're actually
prepared to live with for the indefinite future, then implement it in
the backend and teach pg_dump about it.

What we *don't* want is a pg_dump representation that exposes
implementation details.  I would classify both the backing table for a
tagged type's enum values, and the representation of its operators and
functions, as implementation details.

BTW, I share Andrew Dunstan's feeling that there's huge overlap here
with support for mysql-like enum types.  We ought to try to kill that
bird with the same stone.

			regards, tom lane

  16. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-21T22:44:22Z

    On Tue, Mar 21, 2006 at 05:25:15PM -0500, Tom Lane wrote:
> The dump should look the same as the commands originally used to create
> the type, which is surely not going to happen with that "SELECT
> create_tagged_type()" stuff barring pg_dump modifications.  Otherwise
> we are nailing down not one but two representations of this feature that
> we'll have to support forevermore: what the users see and what's in
> pg_dump scripts. 

Well, I agree with you in principle there. However, when you install
tsearch2 and do a pg_dump you don't get just a "\i tsearch.sql" out
either (I did propose something like this once though). Now, tsearch2
isn't integrated either, so I guess that the difference.

Let me put it this way: if this is to progress beyond just a contrib
module, it needs to go all the way (special syntax, pg_dump, etc). I'm
not sure if I'm that enamoured with it to want all that.

> Now I happen to think that SELECT create_tagged_type() is a horrid kluge
> anyway ;-) so I'm not proposing that pg_dump be changed to output that.
> What we would need is to design a command syntax that we're actually
> prepared to live with for the indefinite future, then implement it in
> the backend and teach pg_dump about it.

Ack.

> What we *don't* want is a pg_dump representation that exposes
> implementation details.  I would classify both the backing table for a
> tagged type's enum values, and the representation of its operators and
> functions, as implementation details.

One slight problem: the "backing table" is provided by the creator of
the type. And in principle they could be altered once the type is
created. So if the user creates a new tagged type, the pg_dump output
is going to have to output the backing table in some form, if not COPY
then something else.

The only way to avoid that is if both the type and the backing table
are included in the standard distribution and we forbid user changes.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  17. Re: [GENERAL] A real currency type

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-21T22:55:09Z

    Martijn van Oosterhout <kleptog@svana.org> writes:
> Let me put it this way: if this is to progress beyond just a contrib
> module, it needs to go all the way (special syntax, pg_dump, etc). I'm
> not sure if I'm that enamoured with it to want all that.

My feelings in a nutshell ;-)

> The only way to avoid that is if both the type and the backing table
> are included in the standard distribution and we forbid user changes.

I was thinking something more like a CREATE ENUM TYPE command that
specifically lists the enum values, and some extension of that to cater
for tagged types, and the values are put into a system catalog that the
user doesn't manipulate directly.  I don't see why it's a good idea to
put control of the backing table in the user's hands.  Yes, you can
think of advanced applications where it's useful to have random
additional stuff in the table, but that's exactly the point at which you
normally have to get down-and-dirty with some C code --- after all, what
is standardized code going to *do* with the additional stuff?  Nothing,
that's what.  If the argument for this is to make it simple to make
simple enum and tagged types, then I don't think that the design should
be centered on allowing extra stuff.

			regards, tom lane

  18. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-21T23:07:07Z

    On Tue, Mar 21, 2006 at 05:55:09PM -0500, Tom Lane wrote:

<snip>

> Yes, you can
> think of advanced applications where it's useful to have random
> additional stuff in the table, but that's exactly the point at which you
> normally have to get down-and-dirty with some C code --- after all, what
> is standardized code going to *do* with the additional stuff?  Nothing,
> that's what.  If the argument for this is to make it simple to make
> simple enum and tagged types, then I don't think that the design should
> be centered on allowing extra stuff.

Umm, actually that extra info is very useful. For example in the
currency type it can store the currency symbol and how many decimal
places, etc.

The code uses it by converting the datum into a composite form
(base,tag) (the "auxilliary type") or by using the taginfo function
return the info tuple directly. These are easily manipulated by
straight SQL or pl/pgsql functions.

While it may not be critical to the use of then, it's an extremely
handy feature. Half the point of a currency type would be the
formatting, no? The information needs to be stored somewhere...

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  19. Re: [GENERAL] A real currency type

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-21T23:11:12Z

    Martijn van Oosterhout <kleptog@svana.org> writes:
> On Tue, Mar 21, 2006 at 05:55:09PM -0500, Tom Lane wrote:
>> Yes, you can
>> think of advanced applications where it's useful to have random
>> additional stuff in the table, but that's exactly the point at which you
>> normally have to get down-and-dirty with some C code --- after all, what
>> is standardized code going to *do* with the additional stuff?  Nothing,
>> that's what.

> Umm, actually that extra info is very useful. For example in the
> currency type it can store the currency symbol and how many decimal
> places, etc.

And you do what with that in non-currency-specific code?  This argument
isn't holding water for me.  The "decimal places" info is in the wrong
place anyway, it should be attached to the underlying numeric type...

			regards, tom lane

  20. Re: [GENERAL] A real currency type

    Andrew Dunstan <andrew@dunslane.net> — 2006-03-22T02:04:00Z

    Tom Lane said:
> I was thinking something more like a CREATE ENUM TYPE command that
> specifically lists the enum values, and some extension of that to cater
> for tagged types, and the values are put into a system catalog that the
> user doesn't manipulate directly.  I don't see why it's a good idea to
> put control of the backing table in the user's hands.

Me either. I like the sound of this.

cheers

andrew

  21. Modular Type Libraries: was A real currency type

    Trent Shipley <tshipley@deru.com> — 2006-03-22T02:40:38Z

    Without directly addressing the merits of enumerations, enumeration 
interfaces, real currency and time zone types, or whether currency and time 
zone types should be built using enumerations, I would like to ask the 
powers-that-be to seriously consider radically modularizing Postgresql's type 
system.

The core Postgresql installation would come with just those built-in types 
needed to bootstrap itself, perhaps just varchar and an integer type.  
Everything else would be a contributed module.

An interface or contract would be described for creating additional types.  It 
would include things like parameter handlers, how to dump the type, and how 
to load the type.  (That is, standard housekeeping functions needed by the 
Postgresql engine.)

Other that the tiny number of bootstrap types, Postgresql types would 
basically all be contrib modules.

Types could be bundled into groups such as binary, character, numerical, 
2d-spatial, networking, and so on.

Then one would not debate whether a type (or meta-type, like an enumeration) 
should be put into the core product.  Instead, the debate would be whether or 
not to grade the type as "mature" and whether or not to put a given type into 
pre-packaged type libraries with names like "legacy", "sql-2003-standard", or 
"recommended-default".

Power user DBA's could customize the types offered on their systems.

In short:

1) Types would be modular.  This would be elegant, but have no practical 
effect on database performance.    

2) The framework needed to support modular types would encourage type 
development.  This would enhance Postgresql's adaptability which would be A 
Very Good Thing.

  22. Re: Modular Type Libraries: was A real currency type

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-22T03:31:00Z

    Trent Shipley <tshipley@deru.com> writes:
> Without directly addressing the merits of enumerations, enumeration 
> interfaces, real currency and time zone types, or whether currency and time 
> zone types should be built using enumerations, I would like to ask the 
> powers-that-be to seriously consider radically modularizing Postgresql's type
> system.

Er ... what makes you think it's not modular now?

			regards, tom lane

  23. Re: Modular Type Libraries: was A real currency type

    Andrew Dunstan <andrew@dunslane.net> — 2006-03-22T03:34:45Z

    Trent Shipley said:
> Without directly addressing the merits of enumerations, enumeration
> interfaces, real currency and time zone types, or whether currency and
> time  zone types should be built using enumerations, I would like to
> ask the  powers-that-be to seriously consider radically modularizing
> Postgresql's type  system.
>
> The core Postgresql installation would come with just those built-in
> types  needed to bootstrap itself, perhaps just varchar and an integer
> type.   Everything else would be a contributed module.
>
> An interface or contract would be described for creating additional
> types.  It  would include things like parameter handlers, how to dump
> the type, and how  to load the type.  (That is, standard housekeeping
> functions needed by the  Postgresql engine.)
>
> Other that the tiny number of bootstrap types, Postgresql types would
> basically all be contrib modules.
>
> Types could be bundled into groups such as binary, character,
> numerical,  2d-spatial, networking, and so on.
>
> Then one would not debate whether a type (or meta-type, like an
> enumeration)  should be put into the core product.  Instead, the debate
> would be whether or  not to grade the type as "mature" and whether or
> not to put a given type into  pre-packaged type libraries with names
> like "legacy", "sql-2003-standard", or  "recommended-default".
>
> Power user DBA's could customize the types offered on their systems.
>
> In short:
>
> 1) Types would be modular.  This would be elegant, but have no
> practical  effect on database performance.
>
> 2) The framework needed to support modular types would encourage type
> development.  This would enhance Postgresql's adaptability which would
> be A  Very Good Thing.

We already have good support of type development. It's not clear to me that
this would buy us anything at all. It seems like modularisation for the sake
of it. The real issue is what types and type mechanisms should be in the
postgresql core distribution. We won't win any thanks from anyone if we
reduce them. Getting some types right is hard. There is no case that I can
see for pushing timestamps, numerics, bitstrings or geometric or network
types out of the core - they need all the support they can get. I'm also not
sure which of these are required by the SQL spec.

cheers

andrew

  24. Re: Modular Type Libraries: was A real currency type

    Aftab Alam <aalam@tatashare.com> — 2006-03-22T03:37:36Z

    remove my email from the list

Regards,





-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org]On Behalf Of Andrew Dunstan
Sent: Wednesday, March 22, 2006 9:05 AM
To: tshipley@deru.com
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Modular Type Libraries: was A real currency type


Trent Shipley said:
> Without directly addressing the merits of enumerations, enumeration
> interfaces, real currency and time zone types, or whether currency and
> time  zone types should be built using enumerations, I would like to
> ask the  powers-that-be to seriously consider radically modularizing
> Postgresql's type  system.
>
> The core Postgresql installation would come with just those built-in
> types  needed to bootstrap itself, perhaps just varchar and an integer
> type.   Everything else would be a contributed module.
>
> An interface or contract would be described for creating additional
> types.  It  would include things like parameter handlers, how to dump
> the type, and how  to load the type.  (That is, standard housekeeping
> functions needed by the  Postgresql engine.)
>
> Other that the tiny number of bootstrap types, Postgresql types would
> basically all be contrib modules.
>
> Types could be bundled into groups such as binary, character,
> numerical,  2d-spatial, networking, and so on.
>
> Then one would not debate whether a type (or meta-type, like an
> enumeration)  should be put into the core product.  Instead, the debate
> would be whether or  not to grade the type as "mature" and whether or
> not to put a given type into  pre-packaged type libraries with names
> like "legacy", "sql-2003-standard", or  "recommended-default".
>
> Power user DBA's could customize the types offered on their systems.
>
> In short:
>
> 1) Types would be modular.  This would be elegant, but have no
> practical  effect on database performance.
>
> 2) The framework needed to support modular types would encourage type
> development.  This would enhance Postgresql's adaptability which would
> be A  Very Good Thing.

We already have good support of type development. It's not clear to me that
this would buy us anything at all. It seems like modularisation for the sake
of it. The real issue is what types and type mechanisms should be in the
postgresql core distribution. We won't win any thanks from anyone if we
reduce them. Getting some types right is hard. There is no case that I can
see for pushing timestamps, numerics, bitstrings or geometric or network
types out of the core - they need all the support they can get. I'm also not
sure which of these are required by the SQL spec.

cheers

andrew



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org

  25. Re: Modular Type Libraries: was A real currency type

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-03-22T03:39:03Z

    "Andrew Dunstan" <andrew@dunslane.net> writes:
> ... The real issue is what types and type mechanisms should be in the
> postgresql core distribution. We won't win any thanks from anyone if we
> reduce them. Getting some types right is hard. There is no case that I can
> see for pushing timestamps, numerics, bitstrings or geometric or network
> types out of the core - they need all the support they can get. I'm also not
> sure which of these are required by the SQL spec.

Timestamps and numerics are definitely in the spec, geometric and
network types are definitely not.  IIRC, bitstring types are in SQL99
but for some reason are deprecated in SQL2003 (if anyone knows the
reasoning behind the SQL committee's about-face on that, please clue
us in).

AFAICS, the main part of the type system that isn't modular is the
support for type parameters (a/k/a typmod), such as the maximum length
for varchar or the precision/scale for numeric.  We could certainly
invent an API for interpreting such parameters.  But in the current state
of the system the types that have such parameters have to be hard-wired
into the SQL grammar, and I don't see how to get rid of that hard-wiring
without breaking a whole lot of stuff.  Any bison gurus hanging about?

			regards, tom lane

  26. unsubscribe

    Aftab Alam <aalam@tatashare.com> — 2006-03-22T03:40:17Z

    unsubscribe

  27. Re: Modular Type Libraries: was A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-22T09:17:18Z

    On Tue, Mar 21, 2006 at 10:39:03PM -0500, Tom Lane wrote:
> AFAICS, the main part of the type system that isn't modular is the
> support for type parameters (a/k/a typmod), such as the maximum length
> for varchar or the precision/scale for numeric.  We could certainly
> invent an API for interpreting such parameters.  But in the current state
> of the system the types that have such parameters have to be hard-wired
> into the SQL grammar, and I don't see how to get rid of that hard-wiring
> without breaking a whole lot of stuff.  Any bison gurus hanging about?

We've been here before:

http://archives.postgresql.org/pgsql-hackers/2005-08/msg01142.php

Turns out it isn't too hard, except there are a number of limitations.
Unfortunatly I seem to have the deleted the patch I created then. :(

Summerising the thread then:

- It made some changes to what was and wasn't a reserved word.
- Character sets for CHAR types still fall outside the system.
- Some other changes in behaviour

I'm not sure if much has changed since that discussion.

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  28. Re: Modular Type Libraries: was A real currency type

    William ZHANG <uniware@zedware.org> — 2006-03-22T15:53:54Z

    "Tom Lane" <tgl@sss.pgh.pa.us>
> Timestamps and numerics are definitely in the spec, geometric and
> network types are definitely not.  IIRC, bitstring types are in SQL99
> but for some reason are deprecated in SQL2003 (if anyone knows the
> reasoning behind the SQL committee's about-face on that, please clue
> us in).

There is a standard data type called "BIT" in ODBC and JDBC,
but it is sth. like SQL standard's BOOLEAN, not BIT. It seems that
some DBMSs implement BIT as BOOLEAN in the backend. Maybe the standard
committee think that bit string is useless and easy to cause confusion?

Regards,
William ZHANG

  29. Re: Modular Type Libraries: was A real currency type

    Trent Shipley <tshipley@deru.com> — 2006-03-23T00:39:12Z

    On Wednesday 2006-03-22 08:53, William ZHANG wrote:
> "Tom Lane" <tgl@sss.pgh.pa.us>
>
> > Timestamps and numerics are definitely in the spec, geometric and
> > network types are definitely not.  IIRC, bitstring types are in SQL99
> > but for some reason are deprecated in SQL2003 (if anyone knows the
> > reasoning behind the SQL committee's about-face on that, please clue
> > us in).
>
> There is a standard data type called "BIT" in ODBC and JDBC,
> but it is sth. like SQL standard's BOOLEAN, not BIT. It seems that
> some DBMSs implement BIT as BOOLEAN in the backend. Maybe the standard
> committee think that bit string is useless and easy to cause confusion?

In or out of the standard, bitstring can be a nice type to have.

  30. Re: [GENERAL] A real currency type

    August Zajonc <augustz@augustz.com> — 2006-03-24T16:54:54Z

    Martijn van Oosterhout wrote:
> On Tue, Mar 21, 2006 at 03:59:31PM -0500, Andrew Dunstan wrote:
>>> I think such types would be better implemented as some sort of 
>>> structured type, possibly with constructors and methods and all the 
>>> other stuff that SQL talks about.  We don't have all of that yet -- 
>>> maybe we don't need all of it immediately -- but before we start 
>>> endorsing many of these types I'd like to see some consideration given 
>>> to this issue.
>> Yeah, looks interesting but let's find the wrinkles. I was wondering if 
>> it might have relevance to what I wanted to do with enumeration types, 
>> i.e. we would tag each one with its particular enumeration id.
> 
> I have considered making enumeration types with this. It would probably
> work but kind of overkill. The main problem being that this code is
> seriously aimed at creating new types that you intend to do operations
> on, create operators for. An enumeration is just that, an enumeration.
> 
>> I also don't like the idea of it being done with user tables - if this 
>> is core material then the tags should go in the catalog, ISTM.
> 
> But the user is providing the list of tags when creating the type.
> Unless you are proposing copying the tag table to the catalog when the
> type is created? You can't store all the tags for all tagged types in
> one tag table either. At least, that wasn't what I was envisiging. See
> the currency type where the tag-table also stores the format string for
> output. No other tagged type is going to need that information.
> 
> SQL types with constructors and methods might provide this also, I
> don't know. My purpose with this was allowing people to simply create
> new types with specific behaviour without writing functions in C. Right
> now type input/output functions cannot be written in anything other
> than C. Here are a few C functions that handle these new types in a
> generic way and user can write little pl/pgsql wrappers to customise
> the output.
> 
> Have a ncie day,
> 
Martin,

This would be extremely useful to have.

For example, if I store currencies tagged properly, I could do a select
and multiply the currency tag by the factor associated with that tag.
This would allow easily quoting financials in a standard currency, but
protect against type mismatches presumably at the database level (USD +
AUD fails).

Either way, it should be easy to add tags (ie, INSERT or similar) or get
a list of them (SELECT * from somewhere where type = "currency"). I'm
sure this later will be the case system catalog or not.

- August

  31. Re: [GENERAL] A real currency type

    Martijn van Oosterhout <kleptog@svana.org> — 2006-03-24T17:10:00Z

    On Fri, Mar 24, 2006 at 08:54:54AM -0800, August Zajonc wrote:
> Martin,
> 
> This would be extremely useful to have.
> 
> For example, if I store currencies tagged properly, I could do a select
> and multiply the currency tag by the factor associated with that tag.
> This would allow easily quoting financials in a standard currency, but
> protect against type mismatches presumably at the database level (USD +
> AUD fails).

Yep, it does do that. And it's very handy :)

> Either way, it should be easy to add tags (ie, INSERT or similar) or get
> a list of them (SELECT * from somewhere where type = "currency"). I'm
> sure this later will be the case system catalog or not.

The current implementation allows that. You just insert new tags or
update them.

Anyway, the code works if you want ot try it. Otherwise, maybe someone
will eventually build something equivalent into the backend...

Have a nice day.
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

  32. Re: A real currency type

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-16T19:57:58Z

    I have added this URL to the TODO list in case people want to move
forward on this.

---------------------------------------------------------------------------

Martijn van Oosterhout wrote:
-- Start of PGP signed section.
> For a while I've been wondering about making a type that was really a
> shell around a base type that tagged the type in some way. For example,
> associating a currency with a numeric and complaining about additions
> between mismatches.
> 
> Well, I did it and it's available here:
> http://svana.org/kleptog/pgsql/taggedtypes.html
> 
> Below some examples of it in action. Yes, that's a timestamp that
> remembers the timezone. Neat huh?
> 
> Tested on 7.4 and a recent 8.1devel so it should work for most people.
> Installation reports welcome. Note, this is beta software, don't run it
> on your production server. Thanks.
> 
> Have a nice day,
> 
> 
> test=# select '5.6 USD'::currency + '4.5 USD'::currency;;
>  ?column?  
> -----------
>  10.10 USD
> (1 row)
> 
> test=# select '5.6 USD'::currency + '4.5 AUD'::currency;;
> ERROR:  Using operator +(currency,currency) with incompatable tags (USD,AUD)
> test=# select c1, print_currency(c1) from c;
>      c1     | print_currency 
> ------------+----------------
>  232.44 USD | US$    232.44
>  21.20 EUR  | ?       21.20
>  -13.44 AUD | AU$    -13.44
>  0.01 USD   | US$      0.01
>  14.00 AUD  | AU$     14.00
> (5 rows)
> 
> test=# select 5.4*c1 from c where tag(c1) = 'AUD';
>   ?column?  
> ------------
>  -72.58 AUD
>  75.60 AUD
> (2 rows)
> 
> test=# select t, "timestamp"(t), date_part('hour',t) from c;
>                     t                    |      timestamp      | date_part 
> -----------------------------------------+---------------------+-----------
>  2005-08-14 02:00:00+02 Europe/Amsterdam | 2005-08-14 02:00:00 |         2
>  2005-08-14 02:00:00+02 Australia/Sydney | 2005-08-14 10:00:00 |        10
>  2005-08-14 02:00:00+02 Asia/Hong_Kong   | 2005-08-14 08:00:00 |         8
>  2005-08-14 02:00:00+02 America/New_York | 2005-08-13 20:00:00 |        20
>  2005-08-14 02:00:00+02 Asia/Kuwait      | 2005-08-14 03:00:00 |         3
> (5 rows)
> 
> -- 
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> > tool for doing 5% of the work and then sitting around waiting for someone
> > else to do the other 95% so you can sue them.
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +