Re: timestamp/function question

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!