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...?
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...?
BETTER YET!
edit my example code so it works and post it to this list so everyone else
can have a function that uses ''now'' as actually "this very moment in
time", rather than "the moment the main parser made 'now' a constant".
Yeesh!
Import Notes
Resolved by subject fallback
On Wed, Mar 28, 2001 at 11:46:42PM -0800, Soma Interesting wrote:
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...?BETTER YET!
edit my example code so it works and post it to this list so everyone else
can have a function that uses ''now'' as actually "this very moment in
time", rather than "the moment the main parser made 'now' a constant".Yeesh!
try
\df time
and see what's available. notably,
select timeofday(),timenow();
--
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!
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
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 wantRETURN 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 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!
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 wantRETURN 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...
Show quoted text
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!