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

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



view thread (11+ messages)  latest in thread

Message-ID: <3B3A265E.E13F3944@archonet.com>
Permalink:  ../3B3A265E.E13F3944@archonet.com/
Also on:    postgresql.org/message-id/3B3A265E.E13F3944@archonet.com

 · 

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

* 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