Using a parameter in Interval

From: "Davidson, Robert" <robdavid(at)amazon(dot)com>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Using a parameter in Interval
Date: 2006-03-22 00:52:45
Message-ID: 8333C841129E074E9F83FC80676BA76E0BEBEF@exchg-sea3-03.ant.amazon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Michael Glaesemann 2006-03-22 00:56:46 Re: Using a parameter in Interval
Previous Message Scott Marlowe 2006-03-21 19:08:08 Re: Referential integrity broken (8.0.3), sub-select help