date formatting and tab-complete patch

Guys, attached is a patch against the 7.2b3 source tree which improves
the i18n of the date formatting functions, using the nl_langinfo(3)
function, together with an autoconf macro to test it's availability,
also an small change to the tab-complete feature of psql which allows
to use tables and views interchangeably. Please consider the
incorporation of those, small but useful changes, in the upcoming 7.2
release.

Sorry, too late for 7.2. Added to queue for 7.3:

http://216.55.132.35/cgi-bin/pgpatches2

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Guys, attached is a patch against the 7.2b3 source tree which improves
the i18n of the date formatting functions, using the nl_langinfo(3)
function, together with an autoconf macro to test it's availability,
also an small change to the tab-complete feature of psql which allows
to use tables and views interchangeably. Please consider the
incorporation of those, small but useful changes, in the upcoming 7.2
release.

Sorry, too late for 7.2. Added to queue for 7.3:

Oh I see, - maybe for 7.2.1? ;-) - .Any way I miss the pg_config.h.in
patch. Attached.

Regards,
Manuel.

Sorry, too late for 7.2. Added to queue for 7.3:

Oh I see, - maybe for 7.2.1? ;-) - .Any way I miss the pg_config.h.in
patch. Attached.

OK, got it; now at same location:

http://216.55.132.35/cgi-bin/pgpatches2

We don't add features in 7.X.X releases, so it will have to wait for
7.3. Sorry.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman@candle.pha.pa.us> writes:

We don't add features in 7.X.X releases, so it will have to wait for
7.3. Sorry.

That's OK for me, since I already maintain my own version of postgres
with "features" that may not be useful for every one, or not
acceptable due "backwards compatibility" constrains. However waiting
about a year for those small changes to appear in the official
release is, IMHO, too much waiting :-(.

Regards,
Manuel.

Bruce Momjian <pgman@candle.pha.pa.us> writes:

We don't add features in 7.X.X releases, so it will have to wait for
7.3. Sorry.

That's OK for me, since I already maintain my own version of postgres
with "features" that may not be useful for every one, or not
acceptable due "backwards compatibility" constrains. However waiting
about a year for those small changes to appear in the official
release is, IMHO, too much waiting :-(.

Yes, I understand.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Manuel Sugawara <masm@fciencias.unam.mx> writes:

That's OK for me, since I already maintain my own version of postgres
with "features" that may not be useful for every one, or not
acceptable due "backwards compatibility" constrains. However waiting
about a year for those small changes to appear in the official
release is, IMHO, too much waiting :-(.

The interval between major versions is supposed to be 4 months or so,
not a year. I admit we've had pretty awful luck at keeping to schedule
the past couple of cycles --- but that's not a reason to change the
schedule target, and definitely not a reason to cause the schedule to
slip more by ignoring the feature-freeze rule after beta starts.

regards, tom lane

Hi there,

I tried using the binary large object support of Postgresql but it
seems to be different from the one supported by Interbase. Is there a way
by which I can get rid of the lo_export and lo_import way of doing blob
in PostgreSQL?

Thanks a lot...

manny

Manuel Sugawara writes:

Guys, attached is a patch against the 7.2b3 source tree which improves
the i18n of the date formatting functions, using the nl_langinfo(3)
function,

What's the effect of that?

together with an autoconf macro to test it's availability,

Please put macros into config/*.m4 (probably c-library.m4).

also an small change to the tab-complete feature of psql which allows
to use tables and views interchangeably.

ISTM that in the situation that tab completion covers tables and views are
not interchangeable. Do you have an example?

--
Peter Eisentraut peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:

Manuel Sugawara writes:

Guys, attached is a patch against the 7.2b3 source tree which improves
the i18n of the date formatting functions, using the nl_langinfo(3)
function,

What's the effect of that?

Currently none since I miss another part of the patch :-( -attached-,
but if you add:

setlocale(LC_TIME, "");

in src/backend/main/main.c you will see month and day names printed
according to the current locale.

regress=# select to_char(now(),'fmday dd/month/yyyy');
to_char
--------------------------
martes 04/diciembre/2001
(1 row)

ISTM that in the situation that tab completion covers tables and views are
not interchangeable. Do you have an example?

select * from <TAB>
\d <TAB>

neither works for views; that's annoying, since most access to many
databases are done through views. Actually the only counterexamples I
found are DROP and ALTER TABLE but, may be, that's enough.

Regards,
Manuel.

On Tue, Dec 04, 2001 at 05:57:11PM -0600, Manuel Sugawara wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Manuel Sugawara writes:

Guys, attached is a patch against the 7.2b3 source tree which improves
the i18n of the date formatting functions, using the nl_langinfo(3)
function,

We don't directly call locale stuff in PostgreSQL code. It's
encapsulated in PGLC_ (pg_locale.c) API and all is cached, for
this we use localeconv(3) that returns all in one struct.

(What portability of nl_langinfo()? The localeconv() is ANSI C and
POSIX functions.)

What's the effect of that?

Currently none since I miss another part of the patch :-( -attached-,
but if you add:

setlocale(LC_TIME, "");

I mean is here more reason why not use LC_TIME and LC_NUMERIC in
current sources. A correct solution will "per-columns" locales.

_May be_ all in PostgreSQL is ready (intependent) to LC_TIME, but I
not sure with it. Thomas?

in src/backend/main/main.c you will see month and day names printed
according to the current locale.

regress=# select to_char(now(),'fmday dd/month/yyyy');
to_char
--------------------------
martes 04/diciembre/2001
(1 row)

Sorry didn't see your original patch (I overlook and delete it in my
IMBOX:-(). But I have a question -- do you solve vice versa
conversion from string to timestamp? The basic feature of to_char()
is that all outputs must be possible parse by to_timestamp() with
same format definition:

test=# select to_char('2001-12-05 00:00:00+01'::timestamp,
'fmday dd/month/yyyy');
to_char
-----------------------------
wednesday 05/december /2001
(1 row)

test=# select to_timestamp('wednesday 05/december /2001',
'fmday dd/month/yyyy');
to_timestamp
------------------------
2001-12-05 00:00:00+01
(1 row)

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Karel Zak <zakkr@zf.jcu.cz> writes:

We don't directly call locale stuff in PostgreSQL code. It's
encapsulated in PGLC_ (pg_locale.c) API and all is cached, for
this we use localeconv(3) that returns all in one struct.

(What portability of nl_langinfo()? The localeconv() is ANSI C and
POSIX functions.)

localenconv is posix and ANSI C but it doesn't provide such
functionality (localized month and day names). nl_langinfo conforms to
"The Single UNIX� Specification, Version 2", according to it's manual
page. The portability isn't an issue as long as you provide means to
test and avoid it's use in systems that doesn't provide it. I know
that, at least, Linux and Solaris does, but FreeBSD does not.

[...]

Sorry didn't see your original patch (I overlook and delete it in my
IMBOX:-(). But I have a question -- do you solve vice versa
conversion from string to timestamp? The basic feature of to_char()
is that all outputs must be possible parse by to_timestamp() with
same format definition:

No, however the work seems pretty easy.

test=# select to_char('2001-12-05 00:00:00+01'::timestamp,
'fmday dd/month/yyyy');
to_char
-----------------------------
wednesday 05/december /2001
(1 row)

This example shows another issue. With localized month and day names
the hardcoded paddings doesn't make sense any more since you may have
a month name longer than 9 chars -septiembre- as instance.

If people is interested I may spend some time working with this.

Regards,
Manuel.

On Wed, Dec 05, 2001 at 10:15:37AM -0600, Manuel Sugawara wrote:

Karel Zak <zakkr@zf.jcu.cz> writes:

We don't directly call locale stuff in PostgreSQL code. It's
encapsulated in PGLC_ (pg_locale.c) API and all is cached, for
this we use localeconv(3) that returns all in one struct.

(What portability of nl_langinfo()? The localeconv() is ANSI C and
POSIX functions.)

localenconv is posix and ANSI C but it doesn't provide such
functionality (localized month and day names). nl_langinfo conforms to
"The Single UNIXďż˝ Specification, Version 2", according to it's manual
page. The portability isn't an issue as long as you provide means to
test and avoid it's use in systems that doesn't provide it. I know
that, at least, Linux and Solaris does, but FreeBSD does not.

But we want FreeBSD and others systems too....

[...]

Sorry didn't see your original patch (I overlook and delete it in my
IMBOX:-(). But I have a question -- do you solve vice versa
conversion from string to timestamp? The basic feature of to_char()
is that all outputs must be possible parse by to_timestamp() with
same format definition:

No, however the work seems pretty easy.

test=# select to_char('2001-12-05 00:00:00+01'::timestamp,
'fmday dd/month/yyyy');
to_char
-----------------------------
wednesday 05/december /2001
(1 row)

This example shows another issue. With localized month and day names
the hardcoded paddings doesn't make sense any more since you may have
a month name longer than 9 chars -septiembre- as instance.

Magic "9 chars" is nice and popular Oracle feature :-)

If people is interested I may spend some time working with this.

I already thought about it. You are right it's possible implement,
but I mean not is good if some feature anticipate other matter of
project ... but current to_char(float, '9G999D99') output string
that depend on locale. Why don't allow it for
to_chat(timenstamp, ...) too?

But don't forget it must be fast, portable and locale stuff must
cached and encapsulated in pg_locale.c.

The "9 chars" can be used for English locales only.

Comments?

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Karel Zak <zakkr@zf.jcu.cz> writes:

This example shows another issue. With localized month and day names
the hardcoded paddings doesn't make sense any more since you may have
a month name longer than 9 chars -septiembre- as instance.

Magic "9 chars" is nice and popular Oracle feature :-)

You think so?, May be it was in the teletype or monospaced display
times. But now? I don't think so.

The "9 chars" can be used for English locales only.

May be add a GUC, something like useless_oracle_compatibility turned
on by default (for the backwards compatibility). Believe me, the
paddings are mostly useless those days where aplications tends to use
more the web or graphical interfaces than terminals or teletypes.

Regards,
Manuel.

On Wed, Dec 05, 2001 at 11:33:26AM -0600, Manuel Sugawara wrote:

Karel Zak <zakkr@zf.jcu.cz> writes:

This example shows another issue. With localized month and day names
the hardcoded paddings doesn't make sense any more since you may have
a month name longer than 9 chars -septiembre- as instance.

Magic "9 chars" is nice and popular Oracle feature :-)

It was irony :-) I don't know why Oracle has this stupid feature, but
if we want be compatible, we must use it too....

You think so?, May be it was in the teletype or monospaced display
times. But now? I don't think so.

The "9 chars" can be used for English locales only.

May be add a GUC, something like useless_oracle_compatibility turned
on by default (for the backwards compatibility). Believe me, the

I not sure with some global switch like 'useless_oracle_compatibility',
here must be way how use old and new features together without
restriction.

paddings are mostly useless those days where aplications tends to use
more the web or graphical interfaces than terminals or teletypes.

Yesterday I forgot important note: if to_char() will support locales
for datetime output it must be implemented as _new_ feature. It means
this new implementation must be backward compatible for all current
format definition. For example:

to_char(now(), 'Month') _must_forever_output_: 'December '

and this output must be independent on locales setting. It's
important, because a lot of application depend of current output
format. If someone wants use locale depend output must be possible
set it by some format suffix, for example:

to_char(now(), 'LCMonth')

(to_char() already knows work with suffixes, for example FM). And must
be possible mix it in one format definition:

to_char(now(), 'LCMonth Month')

-output-> 'Xxxxxx December '

where 'Xxxxxx' is the Month from locales and 'December ' is output
compatible with current (Oracle) to_char() and is locale independen.

This solve a problem with "9 Chars", because all non-locales output
or compilation without locales support will use it like now.

It's very simular to number formatting by to_char(). There is two
patterns for decimal point, 'D' -- for locale depend deciaml point
and '.' -- for "standard" locales independend. With Months/Days it
must be same.

Right?

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Karel Zak writes:

(What portability of nl_langinfo()? The localeconv() is ANSI C and
POSIX functions.)

To use the table of contents of the GNU libc manual:

* The Lame Way to Locale Data:: ISO C's `localeconv'.
* The Elegant and Fast Way:: X/Open's `nl_langinfo'.

--
Peter Eisentraut peter_e@gmx.net

Karel Zak <zakkr@zf.jcu.cz> writes:

[...]

and this output must be independent on locales setting. It's
important, because a lot of application depend of current output
format. If someone wants use locale depend output must be possible
set it by some format suffix, for example:

to_char(now(), 'LCMonth')

I was thinking it and I do not like much the idea to add a new
prefix. I would like more a new set of functions:

* lto_char (date, format [,locale])
* lto_date (date,format [,locale])
* lto_timestamp (timestamp,format [,locale])

Also if we are filling with spaces in the present code we would have
to also do it with the locale aware code. Seems to me that this is a
better way to keep backwards compatibility, so

lto_char('1974/10/22'::date,'dd/month/yyyy','es_MX')

would lead to '22/octubre /1974', filled to 10, since 'septiembre'
which has 10 chars is the longest month name in the 'es' localization,
so people can still use 'FM' if they want '22/octubre/1974'. Another
issue is that capilalization is also locale depenent, as instance in
spanish we don't capitalize month names, but my crystal ball sees
people complaining because 'Month' doesn't capitalize, so in any case
we need something like a prefix to indicate that we do not want it in
capital letters, nor in small letters, but in the form that the
localization in course prefers, maybe something like 'lmonth'.

Comments?

Regards,
Manuel.

PS. By the way, reviewing the code I found a bug that causes the
following test:

to_date(to_char('1979/22/10'::date,'dd/fmrm/yyyy'),'dd/fmrm/yyyy') = '1979/22/10'::date;

give an error instead of a true value. Attached is the patch to fix it
against the 7.2b3 tree.

On Sat, Dec 08, 2001 at 07:41:01PM -0600, Manuel Sugawara wrote:

Karel Zak <zakkr@zf.jcu.cz> writes:

[...]

and this output must be independent on locales setting. It's
important, because a lot of application depend of current output
format. If someone wants use locale depend output must be possible
set it by some format suffix, for example:

to_char(now(), 'LCMonth')

I was thinking it and I do not like much the idea to add a new
prefix. I would like more a new set of functions:

* lto_char (date, format [,locale])
* lto_date (date,format [,locale])
* lto_timestamp (timestamp,format [,locale])

Yes, we can add new function for [,locale], but internaly it
_must_ be same code as current to_ stuff. I think new prefix is no
a problem because it's relevant for Months/Days only.

Also if we are filling with spaces in the present code we would have
to also do it with the locale aware code. Seems to me that this is a
better way to keep backwards compatibility, so

lto_char('1974/10/22'::date,'dd/month/yyyy','es_MX')

would lead to '22/octubre /1974', filled to 10, since 'septiembre'
which has 10 chars is the longest month name in the 'es' localization,

If you use prefix you can write to docs: "LC prefix truncate output
like FM". I mean we needn't support crazy Oracle feature with months
align for locale version of to_char(). If you will use prefix you
can be always sure how is input. I think we can keep this Oracle's
feature only for *non-locale version* and for compatibility only.
I have no idea why support month align in new features where we not
limited with some compatibility...

I still think that 'LC' prefix is good and simply implement-able
idea that allows mix old features with new features, for example:

to_char(now(), '"DE month:" LCMonth "and Oracle month:" Month', de_DE);

BTW the [,locale] feature require improve (very careful) pg_locale
routines because it allows to work with different locales setting
than use actual DB setting.

If user doesn't set locales directly by [,locale] must be possible
for 'LC' prefix use actual locales database setting. For example I
don't want in my applications use harcoded locales setting in queries,
but I want maintain it by DB setting.

PS. By the way, reviewing the code I found a bug that causes the
following test:

to_date(to_char('1979/22/10'::date,'dd/fmrm/yyyy'),'dd/fmrm/yyyy') = '1979/22/10'::date;

give an error instead of a true value. Attached is the patch to fix it
against the 7.2b3 tree.

Thanks. I will repost it to paches list.

Karel

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Please add this _simple_ patch to actual code. It _fix_ my bug
in formatting.c.

Thanks to Manuel Sugawara <masm@fciencias.unam.mx>.

Karel

On Sat, Dec 08, 2001 at 07:41:01PM -0600, Manuel Sugawara wrote:

PS. By the way, reviewing the code I found a bug that causes the
following test:

to_date(to_char('1979/22/10'::date,'dd/fmrm/yyyy'),'dd/fmrm/yyyy') = '1979/22/10'::date;

give an error instead of a true value. Attached is the patch to fix it
against the 7.2b3 tree.

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Can I get some feedback from someone else before applying this? Pretty
late in beta.

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

Please add this _simple_ patch to actual code. It _fix_ my bug
in formatting.c.

Thanks to Manuel Sugawara <masm@fciencias.unam.mx>.

Karel

On Sat, Dec 08, 2001 at 07:41:01PM -0600, Manuel Sugawara wrote:

PS. By the way, reviewing the code I found a bug that causes the
following test:

to_date(to_char('1979/22/10'::date,'dd/fmrm/yyyy'),'dd/fmrm/yyyy') = '1979/22/10'::date;

give an error instead of a true value. Attached is the patch to fix it
against the 7.2b3 tree.

--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 3: 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 Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026