Received: from anchor-post-30.mail.demon.net (anchor-post-30.mail.demon.net [194.217.242.88]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5RJ3Aa43761 for ; Wed, 27 Jun 2001 15:03:10 -0400 (EDT) (envelope-from dev@archonet.com) Received: from mwynhau.demon.co.uk ([193.237.186.96] helo=mainbox.archonet.com) by anchor-post-30.mail.demon.net with esmtp (Exim 2.12 #1) id 15FKcm-0001Ib-0U; Wed, 27 Jun 2001 20:05:17 +0100 Received: from archonet.com (ibook.archonet.com [192.168.1.32]) by mainbox.archonet.com (Postfix) with ESMTP id AFAE412EEF; Wed, 27 Jun 2001 19:33:07 +0100 (BST) Message-ID: <3B3A265E.E13F3944@archonet.com> Date: Wed, 27 Jun 2001 19:30:54 +0100 From: Richard Huxton X-Mailer: Mozilla 4.7C-CCK-MCD {C-UDP; EBM-APPLE} (Macintosh; I; PPC) X-Accept-Language: en MIME-Version: 1.0 To: Kristis Makris Cc: pgsql-sql@postgresql.org Subject: Re: Using the extract() function in plpgsql References: <200106202233.f5KMXSa91452@postgresql.org> <003701c0fa5e$722570a0$1001a8c0@archonet.com> <200106261958.f5QJwsa45412@postgresql.org> <3B398892.D0001CDE@archonet.com> <200106271556.f5RFu4a53609@postgresql.org> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200106/436 X-Sequence-Number: 3733 Kristis Makris wrote: > > 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: Tested here and you're right - there are 2 issues: 1. drop table not working - I'll check the docs on this 2. bad oid reference - even if we did recreate the table, the reference to it is compiled in after the first run. Don't see a way around this one. I've gone back and looked at your initial email (tuned in halfway through this - sorry). It looks like all you want to do is get the number of seconds difference between two times into a variable - is that right? If so, all you need to do is use the following code. I got a bit mislead by the whole CREATE TABLE AS business earlier. DROP FUNCTION sel_in(timestamp, timestamp); CREATE FUNCTION sel_in(timestamp, timestamp) returns int4 as ' DECLARE fromdt ALIAS FOR $1; todt ALIAS FOR $2; diff interval; idiff int4; myrec record; BEGIN diff := todt - fromdt; RAISE NOTICE ''diff = %'',diff; idiff:= extract(epoch from diff); RAISE NOTICE ''idiff = %'',idiff; RETURN idiff; END; ' language 'plpgsql'; select sel_in(now(), '2001-06-27 19:27:00+01'::timestamp); You don't actually need to use a select at all, just assignment. Is this more what you were after? - Richard Huxton