From: Richard Huxton <dev@archonet.com>
To: Kristis Makris <kristis.makris@datasoft.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using the extract() function in plpgsql
Date: Wed, 27 Jun 2001 19:30:54 +0100
Message-ID: <3B3A265E.E13F3944@archonet.com> (raw)
References: <200106202233.f5KMXSa91452@postgresql.org>
<003701c0fa5e$722570a0$1001a8c0@archonet.com>
<200106261958.f5QJwsa45412@postgresql.org>
<3B398892.D0001CDE@archonet.com>
<200106271556.f5RFu4a53609@postgresql.org>
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
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: dev@archonet.com, kristis.makris@datasoft.com
Subject: Re: Using the extract() function in plpgsql
In-Reply-To: <3B3A265E.E13F3944@archonet.com>
* 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