From: Kristis Makris <kristis.makris@datasoft.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using the extract() function in plpgsql
Date: 27 Jun 2001 08:55:57 -0700
Message-ID: <200106271556.f5RFu4a53609@postgresql.org> (raw)
In-Reply-To: <3B398892.D0001CDE@archonet.com>
References: <200106202233.f5KMXSa91452@postgresql.org>
<003701c0fa5e$722570a0$1001a8c0@archonet.com>
<200106261958.f5QJwsa45412@postgresql.org>
<3B398892.D0001CDE@archonet.com>
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 '
DECLARE
lFromDate ALIAS FOR $1;
lToDate ALIAS FOR $2;
lFlow ALIAS FOR $3;
lACFTProduced NUMERIC;
lDateInterval INTERVAL;
lSeconds INT4;
lExecStmt TEXT;
BEGIN
lDateInterval = 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 id
INTO lSeconds
FROM lTemp;
RAISE NOTICE ''The difference interval is: %'', lDateInterval;
RAISE NOTICE ''The seconds 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: 6019015
acftproduced
--------------
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kristis.makris@datasoft.com, dev@archonet.com
Subject: Re: Using the extract() function in plpgsql
In-Reply-To: <200106271556.f5RFu4a53609@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox