Received: from datasoft.com (maniac.datasoft.com [63.228.155.187]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5RFu4a53609 for ; Wed, 27 Jun 2001 11:56:04 -0400 (EDT) (envelope-from kristis.makris@datasoft.com) Message-Id: <200106271556.f5RFu4a53609@postgresql.org> Received: (qmail 6229 invoked from network); 27 Jun 2001 15:55:57 -0000 Received: from dsco-user.datasoft.com (HELO leonidas.corp.datasoft.com) (63.228.155.190) by maniac.datasoft.com with SMTP; 27 Jun 2001 15:55:57 -0000 Subject: Re: Using the extract() function in plpgsql From: Kristis Makris To: Richard Huxton Cc: pgsql-sql@postgresql.org 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> Content-Type: text/plain X-Mailer: Evolution (0.9/+cvs.2001.03.14.20.47 - Preview Release) Date: 27 Jun 2001 08:55:57 -0700 Mime-Version: 1.0 X-Archive-Number: 200106/434 X-Sequence-Number: 3731 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