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. +

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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 isa
> > > 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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEIA0+IB7bNG8LQkwRArqvAJ9w4qOV2JAwAzq0PW86OU 1FoNdScQCfa08A
w3Pz3MDRMBGdrs0ObXg+AeI=
=do1B
-----END PGP SIGNATURE-----

>> 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


---------------------------(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

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(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

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)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFEIE9VIB7bNG8LQkwRAvlHAJ9y6BgYQSJ2BeGwamNvWz a+IH6C9wCfSV8D
21YXhJYA0XdiFZopdocO0lY=
=GGiT
-----END PGP SIGNATURE-----

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/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(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