Re: Using the extract() function in plpgsql

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