Hi all,

I cannot to get this to run... I think I am mistaking some basic concept or I have a big brain-lock . Somebody know what is the problem to execute this function ?

Thanks in advance...

--====================================
DROP TABLE test;
CREATE TABLE test
(
clave numeric(7,0) not null,
PRIMARY KEY (clave)
) WITHOUT OIDS;
--====================================
DROP FUNCTION f_test(numeric(7,0), numeric(7,0));
CREATE OR REPLACE FUNCTION f_test(numeric(7,0), numeric(7,0)) RETURNS timestamp AS '
DECLARE
p_datod ALIAS FOR $1;
p_datoh ALIAS FOR $2;
--
tdesde timestamp;
thasta timestamp;
BEGIN
tdesde := now();
--
FOR X IN p_datod..p_datoh LOOP
INSERT INTO test VALUES (x);
END LOOP;
--
thasta := now() - tdesde;
RETURN thasta;
COMMIT;
END;
' LANGUAGE 'plpgsql';
--====================================
select f_test(1,9);

Search Discussions

  • Josh Berkus at Sep 10, 2002 at 3:46 pm
    Terry,
    I cannot to get this to run... I think I am mistaking some basic
    concept or I have a big brain-lock . Somebody know what is the
    problem to execute this function ?
    Can you explain what you are trying to accomplish with this function?
    I'll tell you below why it won't work, but to help you find a
    workaround, I'll need to know what you're appempting.
    DROP FUNCTION f_test(numeric(7,0), numeric(7,0));
    CREATE OR REPLACE FUNCTION f_test(numeric(7,0), numeric(7,0))
    -- First off, don't include limits in your function type declarations.
    That is,
    -- use f_test(numeric, numeric) not f_test (numeric(7,0),
    numeric(7,0)).
    -- Type limits are ignored by the function parser, and will sometimes
    cause
    -- errors.

    RETURNS
    timestamp AS '
    DECLARE
    p_datod ALIAS FOR $1;
    p_datoh ALIAS FOR $2;
    --
    tdesde timestamp;
    thasta timestamp;
    BEGIN
    tdesde := now();
    --
    FOR X IN p_datod..p_datoh LOOP
    INSERT INTO test VALUES (x);
    END LOOP;
    --
    thasta := now() - tdesde;
    --Problem #1: A TIMESTAMP minus another TIMESTAMP returns an
    --INTERVAL, not a TIMESTAMP. See my paper on timestamps and
    --intervals on http://techdocs.postgresql.org/

    --Problem #2: since functions are inherently a single
    --transaction, the values of global database variables -- such as NOW()

    --are frozen at the beginning of the function. Thus, the function as
    you
    --have written it will always return an interval of 0:00
    RETURN thasta;
    COMMIT;
    --Problem #3: A commmit statement is entirely superflous within a
    --function, which is transactional regardless, and will cause an error.
    END;
    ' LANGUAGE 'plpgsql';
    --====================================
    select f_test(1,9);
    -Josh Berkus
  • Terry Yapt at Sep 10, 2002 at 5:58 pm

    Josh Berkus wrote:

    Terry,

    Can you explain what you are trying to accomplish with this function?
    I'll tell you below why it won't work, but to help you find a
    workaround, I'll need to know what you're appempting.
    I am only testing different datatypes. In the example I would like to know how many time was spent by the function execution.
    -- First off, don't include limits in your function type declarations.
    That is,
    -- use f_test(numeric, numeric) not f_test (numeric(7,0),
    numeric(7,0)).
    -- Type limits are ignored by the function parser, and will sometimes
    cause
    -- errors.
    Ok.. thanks...
    RETURNS
    timestamp AS '
    DECLARE
    p_datod ALIAS FOR $1;
    p_datoh ALIAS FOR $2;
    --
    tdesde timestamp;
    thasta timestamp;
    BEGIN
    tdesde := now();
    --
    FOR X IN p_datod..p_datoh LOOP
    INSERT INTO test VALUES (x);
    END LOOP;
    --
    thasta := now() - tdesde;
    --Problem #1: A TIMESTAMP minus another TIMESTAMP returns an
    --INTERVAL, not a TIMESTAMP. See my paper on timestamps and
    --intervals on http://techdocs.postgresql.org/
    Sorry. I had no luck looking for your document... :-(
    --Problem #2: since functions are inherently a single
    --transaction, the values of global database variables -- such as NOW()

    --are frozen at the beginning of the function. Thus, the function as
    you
    --have written it will always return an interval of 0:00
    Hummm... ok. I have read this in somewhere. But I have too much information about pgsql to digest (for the moment).
    But then... How could I obtain "variable global values" (like the timestamp for example) in different function locations whit its "at time" values ???
    RETURN thasta;
    COMMIT;
    --Problem #3: A commmit statement is entirely superflous within a
    --function, which is transactional regardless, and will cause an error.
    But...then..
    Do you mean all or nothing of the function statements will be executed regardless how many tables and/or sentences will be involved ????
    END;
    ' LANGUAGE 'plpgsql';
    --====================================
    select f_test(1,9);
    Is, at least, the above statement right ?????? ;-) <g>

    It is a joke.. thanks for your help Josh...
  • Josh Berkus at Sep 10, 2002 at 6:12 pm
    Terry,
    --Problem #1: A TIMESTAMP minus another TIMESTAMP returns an
    --INTERVAL, not a TIMESTAMP. See my paper on timestamps and
    --intervals on http://techdocs.postgresql.org/
    Sorry. I had no luck looking for your document... :-(
    The document in your 2nd e-mail is the correct one.
    Hummm... ok. I have read this in somewhere. But I have too much
    information about pgsql to digest (for the moment).
    But then... How could I obtain "variable global values" (like the timestamp
    for example) in different function locations whit its "at time" values ???

    Not that I know of. I'd suggest, instead, that you call the second now()
    statement after the function completes.
    --Problem #3: A commmit statement is entirely superflous within a
    --function, which is transactional regardless, and will cause an error.
    But...then..
    Do you mean all or nothing of the function statements will be executed
    regardless how many tables and/or sentences will be involved ????

    That is correct. If you need conditional commiting or exception-handling,
    use an external middleware language such as Java, Perl, or Python.

    --
    -Josh Berkus
    Aglio Database Solutions
    San Francisco
  • Tom Lane at Sep 10, 2002 at 7:27 pm

    Josh Berkus writes:
    But then... How could I obtain "variable global values" (like the timestamp
    for example) in different function locations whit its "at time" values ???
    Not that I know of. I'd suggest, instead, that you call the second now()
    statement after the function completes.
    There is a function that gives true realtime, rather than transaction
    start time as now() does. I think it's called timeofday(), but check
    the manual.

    regards, tom lane
  • Terry Yapt at Sep 10, 2002 at 10:38 pm
    Hi Tom...

    Here is the complete function modified and running fine now with its result on my test computer.

    Thanks Tom, Josh and best regards.....

    --====================================
    DROP TABLE test;
    CREATE TABLE test
    (
    clave numeric(7,0) not null,
    PRIMARY KEY (clave)
    ) WITHOUT OIDS;
    --====================================
    DROP FUNCTION f_test(numeric, numeric);
    CREATE OR REPLACE FUNCTION f_test(numeric, numeric) RETURNS interval AS '
    DECLARE
    p_datod ALIAS FOR $1;
    p_datoh ALIAS FOR $2;
    --
    tdesde timestamp;
    thasta timestamp;
    BEGIN
    select timeofday() into tdesde;
    --
    FOR X IN p_datod..p_datoh LOOP
    INSERT INTO test VALUES (x);
    END LOOP;
    --
    select timeofday() into thasta;
    --
    RETURN thasta - tdesde;
    END;
    ' LANGUAGE 'plpgsql';
    --====================================
    select f_test(1,9999);

    /*
    f_test
    -----------------
    00:00:02.467566
    (1 row)
    */

    Tom Lane wrote:
    Josh Berkus <josh@agliodbs.com> writes:
    But then... How could I obtain "variable global values" (like the timestamp
    for example) in different function locations whit its "at time" values ???
    Not that I know of. I'd suggest, instead, that you call the second now()
    statement after the function completes.
    There is a function that gives true realtime, rather than transaction
    start time as now() does. I think it's called timeofday(), but check
    the manual.

    regards, tom lane
  • Hubert depesz Lubaczewski at Sep 14, 2002 at 1:13 pm

    On Tue, Sep 10, 2002 at 07:58:22PM +0200, Terry Yapt wrote:
    I am only testing different datatypes. In the example I would like to
    know how many time was spent by the function execution.
    since we were bothered with this too (and measuring outside of function
    was not an option - we had to test how much particular parts of function
    takes time, we (friend of mine to be exact) wrote this code:

    --- getcpuclock.c ---
    #include <postgres.h>
    #include <fmgr.h>

    PG_FUNCTION_INFO_V1(getcpuclock);

    Datum getcpuclock(PG_FUNCTION_ARGS)
    {
    uint64 tsc;

    asm (
    "\n\trdtsc"
    "\n\tmovl\t%%eax,(%0)"
    "\n\tmovl\t%%edx,4(%0)"
    :
    : "cx" (&tsc)
    : "ax", "dx"
    );

    PG_RETURN_INT64(tsc);
    }
    --- getcpuclock.c ---

    this compiled into .so, and installed into postgres with
    CREATE FUNCTION getcpuclock() RETURNS INT8 AS '/home/users/pgdba/work/lib/getcpuclock.so' LANGUAGE 'C';

    (or equivalent with corrected paths)
    will give you function which returns number of processor ticks from last
    bootup.
    this is not easily convertible into seconds or anything else, but
    provides very good accuracy, and is more or less perfect when measuring
    how much time you spend on different tasks.

    hope this helps a bit.

    depesz

    --
    hubert depesz lubaczewski http://www.depesz.pl/
    ------------------------------------------------------------------------
    Mój Boże, spraw abym milczał, dopóki się nie upewnię, że naprawdę mam
    coś do powiedzenia. (c) 1998 depesz
  • Terry Yapt at Sep 10, 2002 at 6:03 pm

    Josh Berkus wrote:

    --Problem #1: A TIMESTAMP minus another TIMESTAMP returns an
    --INTERVAL, not a TIMESTAMP. See my paper on timestamps and
    --intervals on http://techdocs.postgresql.org/
    Perhaps this one ?
    FAQ: Working with Dates and Times in PostgreSQL
    http://techdocs.postgresql.org/techdocs/faqdatesintervals.php
  • Tom Lane at Dec 16, 2004 at 4:47 pm

    Terry Yapt writes:
    I cannot to get this to run... I think I am mistaking some basic concept or I have a big brain-lock . Somebody know what is the problem to execute this function ?
    Perhaps you want to use timeofday() instead of now(). now() does not
    advance within a transaction.

    regards, tom lane
  • Michael Fuhr at Dec 16, 2004 at 6:01 pm
    On Tue, Sep 10, 2002 at 05:32:04PM +0200, Terry Yapt wrote:
    ^^^^^^^^^^^^^^^^^^^^
    Somebody's clock is over two years behind.
    I cannot to get this to run... I think I am mistaking some basic
    concept or I have a big brain-lock . Somebody know what is the
    problem to execute this function ?
    What are you trying to do and what actually happens? Saying simply
    "it doesn't work" means we have to guess. When I run your code in
    PostgreSQL 7.4.6 I get the following:

    SELECT f_test(1,9);
    ERROR: invalid input syntax for type timestamp: "00:00:00"
    CONTEXT: PL/pgSQL function "f_test" line 14 at assignment

    I've found several problems:

    1. Here's line 14:

    thasta := now() - tdesde;

    You're trying to measure how long an operation is taking but you're
    assigning an INTERVAL (the result of the subtraction) to a TIMESTAMP
    variable. I'd suggest declaring the function to return INTERVAL
    and do something like this:

    tdesde := timeofday();
    ...
    thasta := timeofday();
    RETURN thasta - tdesde;

    I changed now() to timeofday() because now() doesn't advance inside
    a transaction.

    2. I'd recommend using TIMESTAMPTZ instead of TIMESTAMP to avoid
    bogus results if the code happens to run across the boundary between
    Summer Time (Daylight Saving Time) and Standard Time.

    3. The function has a COMMIT statement that isn't executed (because
    you RETURN first) but that would cause an error if it did. Functions
    are executed within the outer query's transaction, so you can't do a
    COMMIT or ROLLBACK within the function.

    Hope this helps.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedSep 10, '02 at 3:32p
activeDec 16, '04 at 6:01p
posts10
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase