Thread: Using the extract() function in plpgsql
Hello all,
I'm using PostgreSQL 7.1.2 on i686-pc-linux-gnu, compiled by GCC
egcs-2.91.66
I'm having trouble making use of the extract function in plpgsql. The
examples in the documentation show that the function can be called in
the following way:
SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');
Result: 442800
If the function is called in the above way through the psql client, then
a result is retrieved. But how can the extract() be used in a plpgsql
function without using the EXECUTE statement? Here's what I've been
trying:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE FUNCTION ACFTProduced(TIMESTAMP, TIMESTAMP, DECIMAL(9,1)) RETURNS
DECIMAL(9,2) AS '
DECLARElFromDate ALIAS FOR $1;lToDate ALIAS FOR $2;lFlow ALIAS FOR $3;lACFTProduced NUMERIC;lDateInterval
INTERVAL;lSecondsINT4;lExecStmt TEXT;
BEGINlDateInterval = lToDate - lFromDate;
lExecStmt = ''SELECT extract(epoch from interval '' || '''''''' || lDateInterval || '''''''' || '') INTO
lSeconds'';
----I would like to execute the following statement----SELECT extract(epoch from interval lDateInterval)--INTO
lDays;--
RAISE NOTICE ''The difference interval is: %'', lDateInterval;RAISE NOTICE ''The seconds are: %'', lSeconds;RAISE
NOTICE''The execStmt is: %'', lExecStmt;
EXECUTE lExecStmt;
RETURN lACFTProduced;
END;
' LANGUAGE 'plpgsql';
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If the function is called:
test=# select acftproduced('2001-04-18 16:55:11-07', current_timestamp,
4.5);
NOTICE: The difference interval is: 62 days 16:39:07
NOTICE: The seconds are: <NULL>
NOTICE: The execStmt is: SELECT extract(epoch from interval '62 days
16:39:07') INTO lSeconds
ERROR: EXECUTE of SELECT ... INTO is not implemented yet
Is there any other way the extract() can be called?
Thanks for any help
-Kristis
Kristis Makris writes: > ERROR: EXECUTE of SELECT ... INTO is not implemented yet CREATE TABLE AS -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Hi Peter, Thanks for looking into this. I tried the CREATE TABLE AS syntax within an EXECUTE command but I still get the same error message. The CREATE TABLE AS docs mention that this syntax is "functionalily equivalent" to the SELECT INTO, so it's no surprise that I get the same error message: NOTICE: The execStmt is: CREATE TEMPORARY TABLE test AS SELECT extract(epoch from interval '62 days 22:30:48') ERROR: EXECUTE of SELECT ... INTO is not implemented yet The issue here is: How can I call the extract() function by providing a variable (instead of a hardcoded value) within plpgsql ? The EXECUTE statement facilitates building the *exact* string that should be executed (with no variables interleaved within it), but seems to be coming short on the implementation at the moment. How would the Postgres guys recommend using these functions with variables passed into it instead of hardcoded values? On 20 Jun 2001 23:44:43 +0200, Peter Eisentraut wrote: > Kristis Makris writes: > > > ERROR: EXECUTE of SELECT ... INTO is not implemented yet > > CREATE TABLE AS > > -- > Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter > > ---------------------------------------------------------------
From: "Kristis Makris" <kristis.makris@datasoft.com> > Hi Peter, > > Thanks for looking into this. I tried the CREATE TABLE AS syntax within > an EXECUTE command but I still get the same error message. The CREATE > TABLE AS docs mention that this syntax is "functionalily equivalent" to > the SELECT INTO, so it's no surprise that I get the same error message: > > NOTICE: The execStmt is: CREATE TEMPORARY TABLE test AS SELECT > extract(epoch from interval '62 days 22:30:48') > ERROR: EXECUTE of SELECT ... INTO is not implemented yet In the example above you can just CREATE the table then INSERT into it - avoids the whole issue. Is the example accurate? - Richard Huxton
On 21 Jun 2001 15:28:29 +0100, Richard Huxton wrote: > From: "Kristis Makris" <kristis.makris@datasoft.com> > > > Hi Peter, > > > > Thanks for looking into this. I tried the CREATE TABLE AS syntax within > > an EXECUTE command but I still get the same error message. The CREATE > > TABLE AS docs mention that this syntax is "functionalily equivalent" to > > the SELECT INTO, so it's no surprise that I get the same error message: > > > > NOTICE: The execStmt is: CREATE TEMPORARY TABLE test AS SELECT > > extract(epoch from interval '62 days 22:30:48') > > ERROR: EXECUTE of SELECT ... INTO is not implemented yet > > In the example above you can just CREATE the table then INSERT into it - > avoids the whole issue. Is the example accurate? > > - Richard Huxton Creating the temporary table first, and then inserting into it seems to be a *somehow* functional workaround. However, the function cannot be called again a second time during the same session, since the temporary table already exists. Is there a way a table can be created truly temporary, existing only within the scope of a plpgsql function?
I've a java application use postgresql-7.1.2. I'd like do not allow same user connect to database before close courrent one. i.e. one user one session. because multiple sessions one user always cause trouble. how to do it?? Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
Kristis Makris wrote: > > Creating the temporary table first, and then inserting into it seems to > be a *somehow* functional workaround. However, the function cannot be > called again a second time during the same session, since the temporary > table already exists. Is there a way a table can be created truly > temporary, existing only within the scope of a plpgsql function? You could drop the temporary table at the end of the function, or do you need something more than that? - Richard Huxton
Hi Richard,
On 27 Jun 2001 08:17:38 +0100, Richard Huxton wrote:
> Kristis Makris wrote:
> >
> > Creating the temporary table first, and then inserting into it seems to
> > be a *somehow* functional workaround. However, the function cannot be
> > called again a second time during the same session, since the temporary
> > table already exists. Is there a way a table can be created truly
> > temporary, existing only within the scope of a plpgsql function?
>
> You could drop the temporary table at the end of the function, or do you
> need something more than that?
Actually, I've already tried that and I'm afraid it doesn't work that
way. After I issue the DROP TABLE statement at the end of the function,
the function fails to recreate the table when called again, or at least
fails to reference it properly. Here's the implementation of the
function using a temporary table and attempting to drop it at the end of
the function:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE FUNCTION ACFTProduced(TIMESTAMP, TIMESTAMP, DECIMAL(9,1)) RETURNS
DECIMAL(9,2) AS '
DECLARElFromDate ALIAS FOR $1;lToDate ALIAS FOR $2;lFlow ALIAS FOR $3;lACFTProduced NUMERIC;lDateInterval
INTERVAL;lSecondsINT4;lExecStmt TEXT;
BEGINlDateInterval = lToDate - lFromDate;
-- lExecStmt = ''SELECT extract(epoch from interval '' ||
-- '''''''' || lDateInterval || '''''''' ||
-- '') INTO lSeconds'';
CREATE TEMPORARY TABLE lTemp(id INT4);
lExecStmt = ''INSERT INTO lTemp VALUES(extract(epoch from interval ''
|| '''''''' || lDateInterval || '''''''' || ''))'';
----I would like to execute the following statement----SELECT extract(epoch from interval ''lDateInterval'')--INTO
lSeconds;
RAISE NOTICE ''The execStmt is: %'', lExecStmt;
EXECUTE lExecStmt;
SELECT idINTO lSecondsFROM lTemp;RAISE NOTICE ''The difference interval is: %'', lDateInterval;RAISE NOTICE
''Theseconds are: %'', lSeconds;lACFTProduced = lSeconds * lFlow;
DROP TABLE lTemp;RETURN lACFTProduced;
END;
' LANGUAGE 'plpgsql';
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
And here's the output of the psql client executing the function twice in
the same session:
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands
\g or terminate with semicolon to execute query \q to quit
wats=# select acftproduced('2001-04-18 16:55:11-07', current_timestamp,
4.5);
NOTICE: The execStmt is: INSERT INTO lTemp VALUES(extract(epoch from
interval '69 days 15:56:55'))
NOTICE: The difference interval is: 69 days 15:56:55
NOTICE: The seconds are: 6019015acftproduced
-------------- 27085567.5
(1 row)
wats=# select acftproduced('2001-04-18 16:55:11-07', current_timestamp,
4.5);
NOTICE: The execStmt is: INSERT INTO lTemp VALUES(extract(epoch from
interval '69 days 15:56:56'))
ERROR: Relation 662296 does not exist
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Is plpgsql supposed to behave like this? Is dropping temporary tables
even allowed, or is this a plpgsql bug? I'm running this on a PostgreSQL
7.1.2 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66.
Thanks for your feedback,
-Kristis
Kristis Makris wrote: > > Actually, I've already tried that and I'm afraid it doesn't work that > way. After I issue the DROP TABLE statement at the end of the function, > the function fails to recreate the table when called again, or at least > fails to reference it properly. Here's the implementation of the > function using a temporary table and attempting to drop it at the end of > the function: Tested here and you're right - there are 2 issues: 1. drop table not working - I'll check the docs on this 2. bad oid reference - even if we did recreate the table, the reference to it is compiled in after the first run. Don't see a way around this one. I've gone back and looked at your initial email (tuned in halfway through this - sorry). It looks like all you want to do is get the number of seconds difference between two times into a variable - is that right? If so, all you need to do is use the following code. I got a bit mislead by the whole CREATE TABLE AS business earlier. DROP FUNCTION sel_in(timestamp, timestamp); CREATE FUNCTION sel_in(timestamp, timestamp) returns int4 as ' DECLARE fromdt ALIAS FOR $1; todt ALIAS FOR $2; diff interval; idiff int4; myrec record; BEGIN diff := todt - fromdt; RAISE NOTICE ''diff = %'',diff; idiff:= extract(epoch from diff); RAISE NOTICE ''idiff = %'',idiff;RETURN idiff; END; ' language 'plpgsql'; select sel_in(now(), '2001-06-27 19:27:00+01'::timestamp); You don't actually need to use a select at all, just assignment. Is this more what you were after? - Richard Huxton
Hi Richard, > 1. drop table not working - I'll check the docs on this > 2. bad oid reference - even if we did recreate the table, the reference > to it is compiled in after the first run. Don't see a way around this one. I suspected that since the function is only compiled once, it somehow grabs an oid to be used in the future for the temporary table. Still, though, that makes no sense. I would assume that whenever the CREATE TEMPORARY TABLE stmt is executed, a new oid would be used. Anyway, I'm not familiar with the internals of postgres. > I've gone back and looked at your initial email (tuned in halfway > through this - sorry). It looks like all you want to do is get the > number of seconds difference between two times into a variable - is that right? Yes, that is correct. We got slightly sidetracked here, but the temporary tables creation issue was also a problem I've been dealing with for some time now. > If so, all you need to do is use the following code. I got a bit mislead > by the whole CREATE TABLE AS business earlier. > > DROP FUNCTION sel_in(timestamp, timestamp); > > CREATE FUNCTION sel_in(timestamp, timestamp) returns int4 as ' > DECLARE > fromdt ALIAS FOR $1; > todt ALIAS FOR $2; > diff interval; > idiff int4; > myrec record; > BEGIN > diff := todt - fromdt; > RAISE NOTICE ''diff = %'',diff; > idiff:= extract(epoch from diff); > RAISE NOTICE ''idiff = %'',idiff; > RETURN idiff; > END; > ' language 'plpgsql'; > > select sel_in(now(), '2001-06-27 19:27:00+01'::timestamp); > > > You don't actually need to use a select at all, just assignment. > > Is this more what you were after? Yap, that's what I was after. After going through the docs I found the following example for the extract(): SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours'); Thus, I was so attempting to use in the plpgsql function the syntax: lSeconds = extract(epoch from interval ''5 days 3 hours''); -- suceeds lSeconds = extract(epoch from interval ''lDifference''); -- fails lSeconds = extract(epoch from interval lDifference); -- fails while the correct syntax is the one you used: lSeconds = extract(epoch from lDifference); ..without the INTERVAL keyword used. It looks like the reason the INTERVAL word is used is for casting the interval in the quotes(otherwise treated as text). This syntax is not acceptable, though, through the psql client. Under the psql client this fails: SELECT EXTRACT(EPOCH FROM '5 days 3 hours'); but this succeeds: SELECT EXTRACT(EPOCH FROM '5 days 3 hours'::interval); It would be nice if the differences between the sql and plpgsql syntax were documented somewhere. Thanks for spending time on this :) -Kristis
Richard Huxton <dev@archonet.com> writes:
> 2. bad oid reference - even if we did recreate the table, the reference
> to it is compiled in after the first run. Don't see a way around this one.
You could work around that by making all the queries referencing the
temp table be built up as strings and EXECUTE'd, rather than just
writing them in-line in the plpgsql code. Pretty grotty but it might
serve for now. Eventually plpgsql needs to have a way to discard
query plans that have been obsoleted by DDL changes.
regards, tom lane
On 27 Jun 2001 16:04:28 -0400, Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: > > 2. bad oid reference - even if we did recreate the table, the reference > > to it is compiled in after the first run. Don't see a way around this one. > > You could work around that by making all the queries referencing the > temp table be built up as strings and EXECUTE'd, rather than just > writing them in-line in the plpgsql code. Pretty grotty but it might > serve for now. Eventually plpgsql needs to have a way to discard > query plans that have been obsoleted by DDL changes. > > regards, tom lane Good point, but this approach might not be able to handle all the queries required to be executed. In particular the EXECUTE of SELECT ... INTO is not implemented yet, as of Postgres 7.1.2. Any thoughts on when this feature will be implemented? 7.1.x ? 7.x ?