Re: Using the extract() function in plpgsql

Hi Richard,


> 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 suspected that since the function is only compiled once, it somehow
grabs an oid to be used in the future for the temporary table. Still,
though, that makes no sense. I would assume that whenever the CREATE
TEMPORARY TABLE stmt is executed, a new oid would be used. Anyway, I'm
not familiar with the internals of postgres.

> 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?


Yes, that is correct. We got slightly sidetracked here, but the
temporary tables creation issue was also a problem I've been dealing
with for some time now.

> 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?

Yap, that's what I was after. After going through the docs I found the
following example for the extract():

SELECT EXTRACT(EPOCH FROM INTERVAL '5 days 3 hours');


Thus, I was so attempting to use in the plpgsql function the syntax:

lSeconds = extract(epoch from interval ''5 days 3 hours'');   -- suceeds
lSeconds = extract(epoch from interval ''lDifference'');      -- fails
lSeconds = extract(epoch from interval lDifference);          -- fails

while the correct syntax is the one you used:

lSeconds = extract(epoch from lDifference);

..without the INTERVAL keyword used. It looks like the reason the
INTERVAL word is used is for casting the interval in the
quotes(otherwise treated as text). This syntax is not acceptable,
though, through the psql client.

Under the psql client this fails:

SELECT EXTRACT(EPOCH FROM '5 days 3 hours');

but this succeeds:

SELECT EXTRACT(EPOCH FROM '5 days 3 hours'::interval);


It would be nice if the differences between the sql and plpgsql syntax
were documented somewhere.

Thanks for spending time on this :)
-Kristis