Horton
Menu

Thread

  1. CURRENT_TIME

    Simeó Reig <simeo@incofisa.com> — 2002-11-06T12:02:28Z

    Hi

I'm upgrading´a production Data Base from 7.1.3 to 7.2.3 under freeBSD

well, the problem is that now in this release CURRENT_TIME return high
precision time, is it possible to have only HH:MM:SS
its for backguards compatibility. I've tried CURRENT_TIME(0)
but it don't work in a function :

CREATE FUNCTION "set_fetxa_mod_entitat" () RETURNS opaque AS '
DECLARE
 idusuari integer;

BEGIN
        SELECT INTO idusuari idoperador from operadors WHERE nomoperador =
(CURRENT_USER)::varchar;
        NEW.horaultimamodificacio = CURRENT_TIME(0);
        NEW.fetxaultimamodificacio = CURRENT_DATE;
        NEW.idoperador = idusuari;
        IF NEW.fetxaultimamodificacio <> OLD.fetxaultimamodificacio THEN
                NEW.numeromodificacions = OLD.numeromodificacions + 1;
        END IF;
        RETURN NEW;
END;
' LANGUAGE 'plpgsql';

And the error is :

postext=# update finques set idoperador=29 where idfinca=22;
NOTICE:  Error occurred while executing PL/pgSQL function
set_fetxa_mod_finca
NOTICE:  line 6 at assignment
ERROR:  Bad time external representation '12:46:25+01'

I need only  12:46:25

Thanks a lot !

  2. Re: CURRENT_TIME

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-06T14:51:16Z

    =?iso-8859-1?Q?Sime=F3_Reig?= <simeo@incofisa.com> writes:
>         NEW.horaultimamodificacio = CURRENT_TIME(0);

> NOTICE:  Error occurred while executing PL/pgSQL function
> set_fetxa_mod_finca
> NOTICE:  line 6 at assignment
> ERROR:  Bad time external representation '12:46:25+01'

I think it will work if you insert an explicit cast:

        NEW.horaultimamodificacio = CURRENT_TIME(0)::time;

(or use CAST() syntax if you prefer).

In the long run, seems like it would be a good idea for type TIME
WITHOUT TIME ZONE's input converter to accept and ignore a timezone
field, just as type TIMESTAMP WITHOUT TIME ZONE does:

regression=# select '2002-11-06 09:48:40.824687-05'::timestamp;
         timestamp
----------------------------
 2002-11-06 09:48:40.824687
(1 row)

regression=# select '09:48:40.824687-05'::time;
ERROR:  Bad time external representation '09:48:40.824687-05'

Thomas, what do you think --- was this behavior deliberate or an
oversight?

			regards, tom lane

  3. Re: CURRENT_TIME

    Simeó Reig <simeo@incofisa.com> — 2002-11-06T19:45:06Z

    One thing I don't understand is why you don't maintained CURRENT_TIME like
7.1.X and CURRENT_TIME(N) with
new behavior,for back compatibility :)

Well, seems like it don't work is there any way to do it ?

I need only HH:MM:SS

Thanks a lot for your time

Postgresql under freeBSD (www.freebsd.org) , 103 tables, more than one
milion rows, 70 concurrent users via ODBC . In production state since april.
Great !

Simeó Reig

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Simeó Reig" <simeo@incofisa.com>
Cc: <pgsql-general@postgresql.org>; "Thomas Lockhart"
<lockhart@fourpalms.org>
Sent: Wednesday, November 06, 2002 3:51 PM
Subject: Re: [GENERAL] CURRENT_TIME


> =?iso-8859-1?Q?Sime=F3_Reig?= <simeo@incofisa.com> writes:
> >         NEW.horaultimamodificacio = CURRENT_TIME(0);
>
> > NOTICE:  Error occurred while executing PL/pgSQL function
> > set_fetxa_mod_finca
> > NOTICE:  line 6 at assignment
> > ERROR:  Bad time external representation '12:46:25+01'
>
> I think it will work if you insert an explicit cast:
>
>         NEW.horaultimamodificacio = CURRENT_TIME(0)::time;
>
> (or use CAST() syntax if you prefer).
>
> In the long run, seems like it would be a good idea for type TIME
> WITHOUT TIME ZONE's input converter to accept and ignore a timezone
> field, just as type TIMESTAMP WITHOUT TIME ZONE does:
>
> regression=# select '2002-11-06 09:48:40.824687-05'::timestamp;
>          timestamp
> ----------------------------
>  2002-11-06 09:48:40.824687
> (1 row)
>
> regression=# select '09:48:40.824687-05'::time;
> ERROR:  Bad time external representation '09:48:40.824687-05'
>
> Thomas, what do you think --- was this behavior deliberate or an
> oversight?
>
> regards, tom lane

  4. Re: CURRENT_TIME

    lockhart@fourpalms.org — 2002-11-07T06:50:00Z

    ...
> In the long run, seems like it would be a good idea for type TIME
> WITHOUT TIME ZONE's input converter to accept and ignore a timezone
> field, just as type TIMESTAMP WITHOUT TIME ZONE does:
...
> Thomas, what do you think --- was this behavior deliberate or an
> oversight?

The behavior was deliberate, but predates the implementation of 
TIMESTAMP WITHOUT TIME ZONE. The time zone is already ignored when 
converting directly from TIME WITH TIME ZONE to TIME WITHOUT TIME ZONE:

lockhart=# select cast(time with time zone '2002-11-06 
22:25:57.796141-05' as time);
       time
-----------------
  22:25:57.796141

and one could claim that this should be allowed from string constants too:

thomas=# select cast('2002-11-06 22:25:57.796141-05' as time);
       time
-----------------
  22:25:57.796141

Patch included to allow this latter case...

                      - Thomas

  5. Failed to initialize lc_messages 7.3b5

    Hervé Piedvache <herve@elma.fr> — 2002-11-07T09:38:18Z

    Hi,

I don't know if it's important or not ... but on my linux Debian woody, with 
fr_FR@euro parameter when I do the initdb I've got a creating template1 
database in /usr/local/pgsql/data/base/1... Failed to initialize lc_messages 
to ''

I just would like to know what that's mean exactly ...

Exact message of initdb :

 /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale fr_FR@euro.
This locale setting will prevent the use of indexes for pattern matching
operations.  If that is a concern, rerun initdb with the collation order
set to "C".  For more information see the Administrator's Guide.

Fixing permissions on existing directory /usr/local/pgsql/data... ok
creating directory /usr/local/pgsql/data/base... ok
creating directory /usr/local/pgsql/data/global... ok
creating directory /usr/local/pgsql/data/pg_xlog... ok
creating directory /usr/local/pgsql/data/pg_clog... ok
creating template1 database in /usr/local/pgsql/data/base/1... Failed to 
initialize lc_messages to ''
ok
creating configuration files... ok
initializing pg_shadow... ok
enabling unlimited row size for system tables... ok
initializing pg_depend... ok
creating system views... ok
loading pg_description... ok
creating conversions... ok
setting privileges on built-in objects... ok
vacuuming database template1... ok
copying template1 to template0... ok

Success. You can now start the database server using:

    /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data
or
    /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

regards,
-- 
Hervé Piedvache

Elma Ingénierie Informatique
6 rue du Faubourg Saint-Honoré
F-75008 - Paris - France
Tel. 33-144949901
fax. 33-144949902

  6. Re: Failed to initialize lc_messages 7.3b5

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-11-07T15:05:28Z

    =?iso-8859-15?q?Herv=E9=20Piedvache?= <herve@elma.fr> writes:
> I don't know if it's important or not ... but on my linux Debian woody, with 
> fr_FR@euro parameter when I do the initdb I've got a creating template1 
> database in /usr/local/pgsql/data/base/1... Failed to initialize lc_messages 
> to ''

> I just would like to know what that's mean exactly ...

It means your platform doesn't accept "fr_FR@euro" as a setting for
LC_MESSAGES.  Since "fr_FR@euro" is evidently accepted as a setting for
other LC_ variables, this is a bug in the locale definition, which you
should report to the Debian folk.

Until they fix it, you can probably work around it by starting the
postmaster with LC_MESSAGES explicitly set to something different than
LANG/LC_ALL are (maybe plain "fr_FR" would work).  I think you will need
to adjust lc_messages in postgresql.conf as well.

			regards, tom lane