Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Using a parameter in Interval
Date: Tue, 21 Mar 2006 16:57:52 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC25E93F@nelson.osl.com>
Thread-Topic: Using a parameter in Interval
Thread-Index: AcZNSu6DV0gw/FIcQguhdVLpFtaIFAAAFMRg
From: "Owen Jacobson" <ojacobson@osl.com>
To: <pgsql-sql@postgresql.org>

Here's one I used to convert an int to an interval in another project:

CREATE OR REPLACE FUNCTION to_interval (sec INTEGER) RETURNS INTERVAL AS =
$$
BEGIN
  RETURN (sec || ' seconds')::INTERVAL;
END;
$$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;

select to_interval (5);
 to_interval
-------------
 00:00:05
(1 row)

You should be able to replace ' seconds' with ' weeks' just fine.

Excuse the outlook-ism,
-Owen
-----Original Message-----
From: pgsql-sql-owner@postgresql.org =
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Davidson, Robert
Sent: Tuesday, March 21, 2006 4:53 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] Using a parameter in Interval


No matter how I try to concatenate, I can't seem to get a parameter to =
be used by INTERVAL in a function:
CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$
BEGIN
        RETURN current_date - INTERVAL (CAST(TrailingWeeks AS varchar) =
|| ' weeks');
END;
$$ LANGUAGE plpgsql;
--select * from testing(1);
ERROR:  syntax error at or near "CAST" at character 34
QUERY:  SELECT  current_date - INTERVAL (CAST( $1  AS varchar) || ' =
weeks')
CONTEXT:  SQL statement in PL/PgSQL function "testing" near line 2
I have tried concatenating it as a declared variable (with and without =
apostrophes)
1 weeks
And=20
'1 weeks'
With no success. Any tips?
Many thanks,
Robert