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