Re: Using the extract() function in plpgsql
От | Kristis Makris |
---|---|
Тема | Re: Using the extract() function in plpgsql |
Дата | |
Msg-id | 200106271556.f5RFu4a53609@postgresql.org обсуждение исходный текст |
Ответ на | Re: Using the extract() function in plpgsql (Richard Huxton <dev@archonet.com>) |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: