pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kristis Makris <kristis.makris@datasoft.com>
To: Richard Huxton <dev@archonet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Using the extract() function in plpgsql
Date: 27 Jun 2001 12:25:09 -0700
Message-ID: <200106271925.f5RJPCa53206@postgresql.org> (raw)
In-Reply-To: <3B3A265E.E13F3944@archonet.com>
References: <200106202233.f5KMXSa91452@postgresql.org>
	<003701c0fa5e$722570a0$1001a8c0@archonet.com>
	<200106261958.f5QJwsa45412@postgresql.org>
	<3B398892.D0001CDE@archonet.com>
	<200106271556.f5RFu4a53609@postgresql.org>
	<3B3A265E.E13F3944@archonet.com>

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




view thread (11+ messages)  latest in thread

Message-ID: <200106271925.f5RJPCa53206@postgresql.org>
Permalink:  ../200106271925.f5RJPCa53206@postgresql.org/
Also on:    postgresql.org/message-id/200106271925.f5RJPCa53206@postgresql.org

 · 

reply

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: kristis.makris@datasoft.com, dev@archonet.com
  Subject: Re: Using the extract() function in plpgsql
  In-Reply-To: <200106271925.f5RJPCa53206@postgresql.org>

* 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