Re: Using the extract() function in plpgsql

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