pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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




view thread (11+ messages)  latest in thread

Message-ID: <200106271556.f5RFu4a53609@postgresql.org>
Permalink:  ../200106271556.f5RFu4a53609@postgresql.org/
Also on:    postgresql.org/message-id/200106271556.f5RFu4a53609@postgresql.org

 · 

reply

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