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