Обсуждение: timestamp/function question
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';
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!
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...?
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
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!
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).
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)
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.
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.
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!