Обсуждение: pl/pgsql and returns timestamp type

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
whatis 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);

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

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
locationswhit 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
beinvolved ???? 

>
> >   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 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

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

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

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

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 <yapt@technovell.com> 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
whatis 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

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.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

rmit.test2115@gmail.com 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
whatis the problem to execute this function ? 

"now() - tdesde" would yield an interval, not a timestamp.

            regards, tom lane

On Thu, Jun 22, 2017 at 9:52 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> rmit.test2115@gmail.com writes:
>> I cannot to get this to run...  I think I am mistaking some basic concept or I have a big brain-lock .  Somebody
knowwhat is the problem to execute this function ? 
>
> "now() - tdesde" would yield an interval, not a timestamp.

Hm, is this a glitch in the matrix or other malfeasance?  gmail
reports the mail as dating from 2002.   Also note the ancient plpgsql
stylings and the dubious email address...

merlin

Merlin Moncure <mmoncure@gmail.com> writes:
> Hm, is this a glitch in the matrix or other malfeasance?  gmail
> reports the mail as dating from 2002.   Also note the ancient plpgsql
> stylings and the dubious email address...

Huh, yeah, I hadn't noticed the date on it, but a closer look at the
Received: lines shows

Received: from mail-ext-test.rmit.edu.au ([131.170.21.118] helo=mail-ext8.rmit.edu.au)
        by makus.postgresql.org with esmtp (Exim 4.84_2)
        (envelope-from <prvs=339241e5b=rmit.test2115@gmail.com>)
        id 1dNuJX-0001Nd-Dw
        for pgsql-novice@postgresql.org; Thu, 22 Jun 2017 05:05:57 +0000
X-SBRS: None
X-IronPort-AV: E=Sophos;i="5.39,371,1493647200";
   d="scan'208";a="2810163"
Received: from unknown (HELO W8108266) ([10.217.132.220])
  by mail-int8.rmit.edu.au with ESMTP; 22 Jun 2017 15:05:50 +1000
Received: via dmail-2002(12) for +lists/pgsql/novice; Tue, 10 Sep 2002 10:32:40 -0500 (CDT)
Received: from relay1.pgsql.com (relay1.pgsql.com [64.49.215.129])
        by lerami.lerctr.org (8.12.2/8.12.2/20020902/$Revision: 1.30 $) with ESMTP id g8AFWXE9015200
        for <ler@lerctr.org>; Tue, 10 Sep 2002 10:32:34 -0500 (CDT)
Received: from postgresql.org (postgresql.org [64.49.215.8])
        by relay1.pgsql.com (Postfix) with ESMTP
        id E418F744506; Tue, 10 Sep 2002 11:32:25 -0400 (EDT)

Somebody at rmit.edu.au reinjected some ancient mail.
Amusingly, our archives seem to have recognized it as a dupe,
even though the mail transport system didn't.

(I wonder why this copy seems to have been delivered to Larry Rosenman
originally...)

            regards, tom lane