473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

INTERVAL in a function

I have a simple function which I use to set up a users' expiry date. If
a field in a table contains an interval then
this function returns a timestamp some time in the future (usually two
weeks), null otherwise. I can't pass the
interval from the table into a variable properly within the function.
Any ideas?

CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS timestamp AS '

DECLARE
grpID ALIAS FOR $1;
intval INTERVAL;
exptime TIMESTAMP;
BEGIN
SELECT INTO intval unitTimeLength::INTERVAL FROM customer.groups WHERE groupsID = grpID;
IF intval IS NULL THEN
RETURN NULL;
ELSE
SELECT INTO exptime current_timestamp + INTERVAL ''intval'';
RETURN exptime;
END IF;
END;
' LANGUAGE 'plpgsql';
SELECT getUnitTimeLength(55);

ERROR: invalid input syntax for type interval: "intval"
CONTEXT: PL/pgSQL function "getunittimelength" line 11 at select into variables
However if I change the else clause to this:
ELSE
SELECT INTO exptime current_timestamp;
RETURN exptime;
END IF;
it works:
----------------------------
2004-11-08 16:14:40.273597
(1 row)
Thanks
Ron


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
2 6908
On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote:
SELECT INTO exptime current_timestamp + INTERVAL ''intval'';


You're using the literal value 'intval' instead of its value, thus
the syntax error. You can simplify the statement to this:

exptime := current_timestamp + intval;

But I think the entire function can be shortened to:

CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS TIMESTAMP AS '
SELECT CURRENT_TIMESTAMP::timestamp + unitTimeLength
FROM customer.groups
WHERE groupsID = $1
' LANGUAGE sql;

You don't need to check for NULL because the result of the addition
will already be NULL if either operand is NULL. Casting CURRENT_TIMESTAMP
is necessary to avoid a "return type mismatch" error.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #2
Michael Fuhr wrote:
On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote:
SELECT INTO exptime current_timestamp + INTERVAL ''intval'';
You're using the literal value 'intval' instead of its value, thus
the syntax error.

Of course, I should have caught that.
You can simplify the statement to this:

exptime := current_timestamp + intval;

But I think the entire function can be shortened to:

CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS TIMESTAMP AS '
SELECT CURRENT_TIMESTAMP::timestamp + unitTimeLength
FROM customer.groups
WHERE groupsID = $1
' LANGUAGE sql;

You don't need to check for NULL because the result of the addition
will already be NULL if either operand is NULL. Casting CURRENT_TIMESTAMP
is necessary to avoid a "return type mismatch" error.

Perfect.
Thanks Michael!
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Kiteman \(Canada\) | last post by:
I have a Reminder feature that I am building into a VB 6.0 program that will (when enabled) remind the user to perform some action. I have a slider where the person can select for the reminder...
1
by: Graeme Longman | last post by:
Hi everyone, I was wondering if anyone has written some Python code which uses a start date and end date and a given interval (day, month or year) and outputs all the time periods for that range...
4
by: Lynn | last post by:
On a form I have Date_Start Date_End I have a new Date_Start1 Date_End1 which the use inputs. I need to validate that Date_Start1 and...
0
by: Johnson, Shaunn | last post by:
Howdy: Running PostgreSQL 7.2.1 on RedHat Linux 7.2. How can I convert data in a table that has been created with the INTERVAL data type into a numeric format? Say, I have a table with this...
3
by: D. Dante Lorenso | last post by:
I was hoping to cast a varchar to an interval. How is this done? Nobody has provided an answer to this questing when asked in the past: ...
1
by: Ed Smith | last post by:
The Postgres INTERVAL literal is not compliant with the ANSI 2003 SQL Spec. Here's the Postgres way: # select INTERVAL '45 DAY'; interval ---------- 45 days (1 row) The spec. says
0
by: Ian E. Morgan | last post by:
After being frustrated with the inflexible output of intervals, I've written a pl/pgsql function to do what I want, and hopefully some other people might find it useful. Output is a string that...
4
by: Materialised | last post by:
Hi Everyone, I am developing a application for my personal use. Basically what it will do is grab still images from my webcam at a set interval of time. And save them to me hard disk. The...
10
by: hazz | last post by:
Is it possible to have the timer interval in a window's service be derived from an app.config setting? When I implemented one a while back I was forced to hard code the value because it wasn't...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.