Re: Using a parameter in Interval

That worked perfectly - thanks!

CREATE OR REPLACE FUNCTION Testing(TrailingWeeks int) RETURNS date AS $$
BEGIN
RETURN current_date - (TrailingWeeks || ' weeks')::INTERVAL;
END;
$$ LANGUAGE plpgsql;

select * from testing(1);

-----Original Message-----
From: pgsql-sql-owner(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org] On Behalf Of Owen Jacobson
Sent: Tuesday, March 21, 2006 4:58 PM
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Using a parameter in Interval

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(at)postgresql(dot)org [mailto:pgsql-sql-owner(at)postgresql(dot)org]On Behalf Of Davidson, Robert
Sent: Tuesday, March 21, 2006 4:53 PM
To: pgsql-sql(at)postgresql(dot)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
'1 weeks'
With no success. Any tips?
Many thanks,
Robert

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq