Обсуждение: 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