Postgres Pro
Downloads
Home   >   mailing lists

Thread: Using a parameter in Interval

Using a parameter in Interval

From
"Davidson, Robert"
Date:
<p align="LEFT"><span lang="en-us"><font face="Arial" size="2">No matter how I try to concatenate, I
can</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">’</font></span><span
lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">t seem to get a parameter to be used by INTERVAL in a
function:</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">CREATE OR REPLACE FUNCTION
Testing(TrailingWeeksint) RETURNS date AS $$</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">BEGIN</font></span><palign="LEFT"><span lang="en-us">        <font face="Arial" size="2">RETURN current_date -
INTERVAL(CAST(TrailingWeeks AS varchar) || ' weeks');</font></span><p align="LEFT"><span lang="en-us"><font
face="Arial"size="2">END;</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">$$ LANGUAGE
plpgsql;</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">--</font></span><span
lang="en-us"></span><spanlang="en-us"><font face="Arial" size="2">select * from testing(1);</font></span><span
lang="en-us"></span><spanlang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">ERROR: 
syntaxerror at or near "CAST" at character 34</font></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">QUERY: SELECT  current_date - INTERVAL (CAST( $1  AS varchar) || ' weeks')</font></span><p align="LEFT"><span
lang="en-us"><fontface="Arial" size="2">CONTEXT:  SQL statement in PL/PgSQL function "testing" near line
2</font></span><spanlang="en-us"></span><span lang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">Ihave tried concatenating it as a declared variable (with and without apostrophes)</font></span><p
align="LEFT"><spanlang="en-us"><font face="Arial" size="2">1 weeks</font></span><p align="LEFT"><span
lang="en-us"><fontface="Arial" size="2">And </font></span><p align="LEFT"><span lang="en-us"><font face="Arial"
size="2">‘</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">1
weeks</font></span><spanlang="en-us"></span><span lang="en-us"><font face="Arial" size="2">’</font></span><span
lang="en-us"></span><spanlang="en-us"></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">With no
success.Any tips?</font></span><p align="LEFT"><span lang="en-us"><font face="Arial" size="2">Many
thanks,</font></span><palign="LEFT"><span lang="en-us"><font face="Arial" size="2">Robert</font></span><span
lang="en-us"></span><spanlang="en-us"></span>

Re: Using a parameter in Interval

From
Michael Glaesemann
Date:
On Mar 22, 2006, at 9:52 , Davidson, Robert wrote:

> 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
The generally recommended way is something like:

test=# select '4'::integer * interval '1 week';
?column?
----------
28 days
(1 row)

or the more SQL compliant:

test=# select cast('4' as integer) * interval '1 week';
?column?
----------
28 days
(1 row)

Does that work for you?

Michael Glaesemann
grzm myrealbox com

Re: Using a parameter in Interval

From
"Owen Jacobson"
Date:
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
'1 weeks'
With no success. Any tips?
Many thanks,
Robert

Re: Using a parameter in Interval

From
Stephan Szabo
Date:
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.

Re: Using a parameter in Interval

From
"Davidson, Robert"
Date:
That worked perfectly - thanks!


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

select * from testing(1);

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] 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: 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
'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