pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUsing a parameter in Interval
5+ messages / 4 participants
[nested] [flat]
* Using a parameter in Interval
@ 2006-03-22 00:52 Davidson, Robert <robdavid@amazon.com>
2006-03-22 00:56 ` Re: Using a parameter in Interval Michael Glaesemann <grzm@myrealbox.com>
2006-03-22 01:03 ` Re: Using a parameter in Interval Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 5+ messages in thread
From: Davidson, Robert @ 2006-03-22 00:52 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using a parameter in Interval
2006-03-22 00:52 Using a parameter in Interval Davidson, Robert <robdavid@amazon.com>
@ 2006-03-22 00:56 ` Michael Glaesemann <grzm@myrealbox.com>
1 sibling, 0 replies; 5+ messages in thread
From: Michael Glaesemann @ 2006-03-22 00:56 UTC (permalink / raw)
To: Davidson, Robert <robdavid@amazon.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using a parameter in Interval
2006-03-22 00:52 Using a parameter in Interval Davidson, Robert <robdavid@amazon.com>
@ 2006-03-22 01:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 5+ messages in thread
From: Stephan Szabo @ 2006-03-22 01:03 UTC (permalink / raw)
To: Davidson, Robert <robdavid@amazon.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using a parameter in Interval
@ 2006-03-22 00:57 Owen Jacobson <ojacobson@osl.com>
0 siblings, 0 replies; 5+ messages in thread
From: Owen Jacobson @ 2006-03-22 00:57 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Using a parameter in Interval
@ 2006-03-22 14:49 Davidson, Robert <robdavid@amazon.com>
0 siblings, 0 replies; 5+ messages in thread
From: Davidson, Robert @ 2006-03-22 14:49 UTC (permalink / raw)
To: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
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@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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-03-22 14:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-03-22 00:52 Using a parameter in Interval Davidson, Robert <robdavid@amazon.com>
2006-03-22 00:56 ` Michael Glaesemann <grzm@myrealbox.com>
2006-03-22 01:03 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-03-22 00:57 Re: Using a parameter in Interval Owen Jacobson <ojacobson@osl.com>
2006-03-22 14:49 Re: Using a parameter in Interval Davidson, Robert <robdavid@amazon.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox