How do I specify an interval in a function?

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