Postgres Pro
Downloads
Home   >   mailing lists

Thread: timestamp/function question

timestamp/function question

From
Soma Interesting
Date:
Why does the following code return the exact same value each time, instead
of a value based on the current time?

CREATE FUNCTION memb_num () RETURNS INT4 AS '
    BEGIN
        RETURN date_part(''epoch'', CURRENT_DATE);
    END;
' LANGUAGE 'plpgsql';

Re: timestamp/function question

From
will trillich
Date:
On Wed, Mar 28, 2001 at 09:55:58PM -0800, Soma Interesting wrote:
> Why does the following code return the exact same value each time, instead
> of a value based on the current time?
>
> CREATE FUNCTION memb_num () RETURNS INT4 AS '
>     BEGIN
>         RETURN date_part(''epoch'', CURRENT_DATE);
>     END;
> ' LANGUAGE 'plpgsql';

this one is covered in the docs, really. lemme see... ruffle
ruffle... here it is:

>>>>>

The type checking done by the Postgres main parser has some side
effects to the interpretation of constant values. In detail there
is a difference between what the two functions

CREATE FUNCTION logfunc1 (text) RETURNS datetime AS '
    DECLARE
        logtxt ALIAS FOR $1;
    BEGIN
        INSERT INTO logtable VALUES (logtxt, ''now'');
        RETURN ''now'';
    END;
' LANGUAGE 'plpgsql';

     and

CREATE FUNCTION logfunc2 (text) RETURNS datetime AS '
    DECLARE
        logtxt ALIAS FOR $1;
        curtime datetime;
    BEGIN
        curtime := ''now'';
        INSERT INTO logtable VALUES (logtxt, curtime);
        RETURN curtime;
    END;
' LANGUAGE 'plpgsql';

do. In the case of logfunc1(), the Postgres main parser knows
when preparing the plan for the INSERT, that the string 'now'
should be interpreted as datetime because the target field of
logtable is of that type. Thus, it will make a constant from it
at this time and this constant value is then used in all
invocations of logfunc1() during the lifetime of the backend.
Needless to say that this isn't what the programmer wanted.

In the case of logfunc2(), the Postgres main parser does not know
what type 'now' should become and therefor it returns a datatype
of text containing the string 'now'. During the assignment to the
local variable curtime, the PL/pgSQL interpreter casts this
string to the datetime type by calling the text_out() and
datetime_in() functions for the conversion.

<<<<<

from
    /usr/share/doc/postgresql-doc/html/user/c40874113.htm
    # this is on my debian 2.2 (potato) system
    # via the 'postgresql-doc' package


--
It is always hazardous to ask "Why?" in science, but it is often
interesting to do so just the same.
        -- Isaac Asimov, 'The Genetic Code'

will@serensoft.com
http://newbieDoc.sourceforge.net/ -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Re: timestamp/function question

From
Soma Interesting
Date:
At 12:41 AM 3/29/2001 -0600, you wrote:
>do. In the case of logfunc1(), the Postgres main parser knows
>when preparing the plan for the INSERT, that the string 'now'
>should be interpreted as datetime because the target field of
>logtable is of that type. Thus, it will make a constant from it
>at this time and this constant value is then used in all
>invocations of logfunc1() during the lifetime of the backend.
>Needless to say that this isn't what the programmer wanted.
>
>In the case of logfunc2(), the Postgres main parser does not know
>what type 'now' should become and therefor it returns a datatype
>of text containing the string 'now'. During the assignment to the
>local variable curtime, the PL/pgSQL interpreter casts this
>string to the datetime type by calling the text_out() and
>datetime_in() functions for the conversion.


blah blah blah <snip>

...and that all meant what? The postgres manual is open to much
interpretation to anyone new trying to understand its contents. Combine
that with documentation that's still not written, or broken across several
different sections (programmer, user, admin, etc) and a search engine which
returns absolute crap.... well I guess us new users can just go use MySQL.

as far as I can tell the above sounds like a complicated  work-around to a
bug, but maybe you'll be kind enough to correct me on this...?

Re: timestamp/function question

From
Tom Lane
Date:
Soma Interesting <dfunct@telus.net> writes:
> Why does the following code return the exact same value each time, instead
> of a value based on the current time?

> CREATE FUNCTION memb_num () RETURNS INT4 AS '
>     BEGIN
>         RETURN date_part(''epoch'', CURRENT_DATE);
>     END;
> ' LANGUAGE 'plpgsql';

Because you asked for a value based on the current *date*.
If you waited till tomorrow and tried again, then you'd get a
different answer.  Perhaps you want

         RETURN date_part(''epoch'', CURRENT_TIMESTAMP);

            regards, tom lane

Re: timestamp/function question

From
will trillich
Date:
On Wed, Mar 28, 2001 at 11:41:28PM -0800, Soma Interesting wrote:
> At 12:41 AM 3/29/2001 -0600, you wrote:
> >do. In the case of logfunc1(), the Postgres main parser knows
> >when preparing the plan for the INSERT, that the string 'now'
> >should be interpreted as datetime because the target field of
> >logtable is of that type. Thus, it will make a constant from it
> >at this time and this constant value is then used in all
> >invocations of logfunc1() during the lifetime of the backend.
> >Needless to say that this isn't what the programmer wanted.
> >
> >In the case of logfunc2(), the Postgres main parser does not know
> >what type 'now' should become and therefor it returns a datatype
> >of text containing the string 'now'. During the assignment to the
> >local variable curtime, the PL/pgSQL interpreter casts this
> >string to the datetime type by calling the text_out() and
> >datetime_in() functions for the conversion.
>
>
> ...and that all meant what? The postgres manual is open to much
> interpretation to anyone new trying to understand its contents. Combine
> that with documentation that's still not written, or broken across several
> different sections (programmer, user, admin, etc) and a search engine which
> returns absolute crap.... well I guess us new users can just go use MySQL.
>
> as far as I can tell the above sounds like a complicated  work-around to a
> bug, but maybe you'll be kind enough to correct me on this...?

i'd agree with you.

but as tom lane mentioned, current_date is today, all day, until
midnight (local time zone, i presume) whereas current_timestamp
is less chunky, having finer grains down to one second.

but even there, the timestamp is apparently (just learned this
today from other posts on this thread) set for the start of the
current transaction (or is it session?)...

timeofday() may be what you're after.

try

    psql
    \df time
    \df current

--
does a brain cell think?

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Re: timestamp/function question

From
Soma Interesting
Date:
At 10:56 AM 3/29/2001 -0500, you wrote:
>Because you asked for a value based on the current *date*.
>If you waited till tomorrow and tried again, then you'd get a
>different answer.  Perhaps you want
>
>                 RETURN date_part(''epoch'', CURRENT_TIMESTAMP);

Thank you Tom!

Thank-you! Thank-you! Thank-you! Thank-you!

How can I possibly thank you enough?! I want you to know how much I
appreciate your answers because I'll have more questions for you one day...

I love postgres, but I hate your documentation... (that doesn't mean I
don't read it however).

Re: timestamp/function question

From
Larry Rosenman
Date:
On the documentation problems, patches are gratefully accepted.  As you
learn more and see problems with the docs, we'd love to get patches.

Larry Rosenman

--
Larry Rosenman                             http://www.lerctr.org/~ler/
Phone: +1 972 414 9812                          E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 US

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 3/29/01, 11:07:17 AM, Soma Interesting <dfunct@telus.net> wrote
regarding Re: [GENERAL] timestamp/function question :


> At 10:56 AM 3/29/2001 -0500, you wrote:
> >Because you asked for a value based on the current *date*.
> >If you waited till tomorrow and tried again, then you'd get a
> >different answer.  Perhaps you want
> >
> >                 RETURN date_part(''epoch'', CURRENT_TIMESTAMP);

> Thank you Tom!

> Thank-you! Thank-you! Thank-you! Thank-you!

> How can I possibly thank you enough?! I want you to know how much I
> appreciate your answers because I'll have more questions for you one
day...

> I love postgres, but I hate your documentation... (that doesn't mean I
> don't read it however).


> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: timestamp/function question

From
Soma Interesting
Date:
At 11:13 AM 3/29/2001 -0600, you wrote:
>i'd agree with you.
>
>but as tom lane mentioned, current_date is today, all day, until
>midnight (local time zone, i presume) whereas current_timestamp
>is less chunky, having finer grains down to one second.
>
>but even there, the timestamp is apparently (just learned this
>today from other posts on this thread) set for the start of the
>current transaction (or is it session?)...
>
>timeofday() may be what you're after.

I'm new to stored procedures, triggers and pl/pgsql. The mistakes I'll
likely make will be simple ones and I should have thought of timestamp vs.
date, sorry to ask such foolish questions.

Re: timestamp/function question

From
Soma Interesting
Date:
At 05:41 PM 3/29/2001 +0000, you wrote:
>On the documentation problems, patches are gratefully accepted.  As you
>learn more and see problems with the docs, we'd love to get patches.

I suppose this often sends people running the other way, but I'd be happy
to once I feel I know what I'm doing ;)

You get the distinct impression those who wrote (much of) the documentation
assume an audience with past Oracle experience. Why can't we all pitch in
and have Bruce write a second edition :)... his book is excellent - just it
doesn't go deeply enough into some of the more advanced topics.

Re: timestamp/function question

From
will trillich
Date:
On Thu, Mar 29, 2001 at 09:44:20AM -0800, Soma Interesting wrote:
> At 11:13 AM 3/29/2001 -0600, you wrote:
> >but as tom lane mentioned, current_date is today, all day,
> >until midnight (local time zone, i presume) whereas
> >current_timestamp is less chunky, having finer grains down to
> >one second.
> >
> >but even there, the timestamp is apparently (just learned this
> >today from other posts on this thread) set for the start of
> >the current transaction (or is it session?)...
> >
> >timeofday() may be what you're after.
>
> I'm new to stored procedures, triggers and pl/pgsql. The
> mistakes I'll likely make will be simple ones and I should have
> thought of timestamp vs.  date, sorry to ask such foolish
> questions.

we may never know for sure, but i'd bet lots-o-moola that there
are folks lurking out there who've been saved hours or days of
hair-pulling by coming across threads such as this...

of course, *i* have never been so naive. ever. not since tuesday.

--
does a brain cell think?

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!