Re: [SQL] Using a parameter in Interval

[Davidson, Robert]

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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Owen Jacobson
Sent: Tuesday, March 21, 2006 4:58 PM
To: pgsql-sql@postgresql.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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 
'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

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

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

Re: [SQL] Using a parameter in Interval

[Stephan Szabo]

On Tue, 21 Mar 2006, Davidson, Robert wrote:

 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?

You'd need a cast, not INTERVAL foo as the latter is for interval literals
(and CAST... is not a valid interval literal even if the output of the
concatenation looks like an interval literal).  I'd go with the suggestion
of using int * interval instead of concatenation in any case.

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

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