Skip site navigation (1) Skip section navigation (2)

How do I specify an interval in a function?

From: "Rob Richardson" <Rob(dot)Richardson(at)rad-con(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: How do I specify an interval in a function?
Date: 2008-07-31 00:24:25
Message-ID: 04A6DB42D2BA534FAC77B90562A6A03D6DE54D@server.rad-con.local (view raw or flat)
Thread:
Lists: pgsql-novice
Greetings!
 
In the help file under date and time functions, I see that intervals can be specified as "interval '3 hours' ".  In a PgAdmin SQL window, I can enter "select interval '3 hours' ", and it will return me "03:00:00", as expected.  I can also enter "select '3 hours'::interval", and get the same result.  Yet neither syntax works inside a function.  
 
declare
     ThreeHours interval;
begin
     ThreeHours = interval '3 hours';  -- throws a syntax error
     ThreeHours = '3 hours'::interval; -- also throws a syntax error
end;
 
So how do I specify an interval in a function?
 
Specifically, I'm trying to do something like the following:
 
    if NewRevisionTime < PredictedEndTime - '08:00:00'::interval then
 
Since both of the shown forms give syntax errors, how do I subtract eight hours from a time???
 
Thank you very much.
 
RobR, thoroughly unimpressed by SQL's support for dates and times

 
 

________________________________

From: pgsql-novice-owner(at)postgresql(dot)org on behalf of Daniel Gour
Sent: Wed 7/30/2008 1:04 PM
To: Frank Bax
Cc: PostgreSQL List - Novice
Subject: Re: [NOVICE] How to copy a schema under another name in same database



Thanks for the suggestion, but I need the application to be multi-platform
(I'm using Qt).  Since sed is not natively available on XP...  Also, as you
mention, it is risky to blindly replace one string for another in the whole
sql script.

The solution is however simple and elegant, maybe someone else will find it
useful.  Thanks!

---------------------------------
Daniel Gour
Adacel Inc.

-----Original Message-----
From: Frank Bax [mailto:fbax(at)sympatico(dot)ca]
Sent: Wednesday, July 30, 2008 12:51 PM
To: PostgreSQL List - Novice
Subject: Re: [NOVICE] How to copy a schema under another name in same
database

Daniel Gour wrote:
> Hello!  I have a PostgreSQL (8.3.3, WinXP + linux) database with
> multiple schemas.  I would like, on a regular basis, to be able to copy
> the structure and data of one schema under a new schema, using of course
> a different name.  What would be the easiest way?



Backup the schema with pg_dump; edit file; change schema name and
restore?  If schema names do not show up in data; then this might work:

pg_dump ... | sed s:schema1:schema2:g | psql


--
Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice

--
Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice


In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2008-07-31 00:59:11
Subject: Re: How do I specify an interval in a function?
Previous:From: Rodrigo E. De León PlicetDate: 2008-07-30 20:08:21
Subject: Re: How to copy a schema under another name in same database

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group